Forum Discussion
Add new calculated column with values from 2 tables (Power Query)
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!!!
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
6 Replies
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(N2). If your dataset is large this can make a huge difference.
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
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
