Add new calculated column with values from 2 tables (Power Query)

[Anonymous]

Hello all, 

I'm really new to Power BI and Power Query and I am now struggling with the following:

Imagine we have Table1 and Table2, which can link by the column "Name":

Table1
Name	Produced Meters
example 1	100
example 2	200

Table2
Name	Run Time
example 1	10
example 2	10

Now I want to calculate in Table2 the "Run Speed" which is "Produced Meters" divided by "Run Time" for each matching row so that the result is the following:

Table2 - after step
Name	Run Time	Run Speed
example 1	10	10
example 2	10	20

How can I do this in only one step??

I have obtained the desired result in more than one step with following code, by adding first the "Produced Meters" column to Table :

#"Add Produced Meters on Table2" = Table.AddColumn(#"Previous Step","Produced Meters",

(x) => Table.First(Table.SelectRows(Table1, each Text.StartsWith(x[Name], [Name])))[Produced Meters], type number)

in 

#"Add Produced Meters on Table2"

And then I can divide by the Run Time of Table 2 and I obtain Run Speed as supposed. 

But how can I do this without adding the "Produced Meters" column first and instead do the calculation right away? I've tried the following code which returns a error:

#"Calculate Run Speed on Table2" = Table.AddColumn(#"Previous Step","Run Speed",

(x) => Table.First(Table.SelectRows(Table1, each Text.StartsWith(x[Name], [Name])))[Produced Meters]/[Run Time], type number)

in 

#"Calculate Run Speed on Table2"

Sorry if this is a bit pedantic but I really want to understand better this for future occasions.

Big thanks in advance for any help!!!

[AlexisOlson]

It's certainly possible to do this in one step (see @BA_Pete's answer) but you should be aware that such a solution is less efficient than a multi-step approach of merging in the column and then dividing.

Merge-then-divide should be a linear complexity O(N) whereas an unoptimized lookup-then-divide is likely quadratic complexity O(N²). If your dataset is large this can make a huge difference.

[Anonymous]

Thank you. I will take this into account and use Merge-then-divide

[BA_Pete]

@AlexisOlson is correct.

I've done the solution as requested as an excercise but, if it was up to me, I'd relate both tables in the data model to a Name dimension table and do it with measures.

Pete

[Anonymous]

I know you already helped me, but could you show me with this example how this would be done? 

I've been working mainly in the Power Query since I don't have much familiarity with DAX and it still kind of confuses me.

Thanks!

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Try adding this as a new custom column in Table2:

let
  currentName = [Name],
  meters = Table.SelectRows(Table1, each [Name] = currentName){0}[Produced Meters]
in
  meters / [Run Time]

This gives me the following output:

Pete

[Anonymous]

Thank you for showing me how it's done.