Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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
Proud to be a Datanaut!
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.
Thank you. I will take this into account and use Merge-then-divide
@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
Proud to be a Datanaut!
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!
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
Proud to be a Datanaut!
Thank you for showing me how it's done.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.