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.
Hi,
I have some related tables:
Transaction Line Items -> Transactions -> Branch Details
I need to create a table in the query editor which includes all the rows from the table "Transaction Line Items" and a column from the "Branch Details" table.
I tried to use the "merge queries" tool but this requires two tables to be directly linked (with 2 columns that match), whereas in my situation there is an intermediary table "Transactions" in the relationship. Using query editor please can you tell me how to merge a column from the table "Branch Details" into the "Transaction Line Items" table?
Thanks,
CM
Solved! Go to Solution.
First merge Transaction with Branch Details.
Next, merge Transaction Item Lines with the result from the previous merge.
let Source = Table.NestedJoin(Transactions,{"Branch ID"},#"Branch Details",{"Branch ID"},"Branch Details",JoinKind.LeftOuter), #"Expanded Branch Details" = Table.ExpandTableColumn(Source, "Branch Details", {"Branch"}, {"Branch"}), #"Merged Queries" = Table.NestedJoin(#"Transaction Line Items",{"Transaction ID"},#"Expanded Branch Details",{"Transaction ID"},"Transaction",JoinKind.LeftOuter), #"Expanded Transaction" = Table.ExpandTableColumn(#"Merged Queries", "Transaction", {"Branch"}, {"Branch"}) in #"Expanded Transaction"
First merge Transaction with Branch Details.
Next, merge Transaction Item Lines with the result from the previous merge.
let Source = Table.NestedJoin(Transactions,{"Branch ID"},#"Branch Details",{"Branch ID"},"Branch Details",JoinKind.LeftOuter), #"Expanded Branch Details" = Table.ExpandTableColumn(Source, "Branch Details", {"Branch"}, {"Branch"}), #"Merged Queries" = Table.NestedJoin(#"Transaction Line Items",{"Transaction ID"},#"Expanded Branch Details",{"Transaction ID"},"Transaction",JoinKind.LeftOuter), #"Expanded Transaction" = Table.ExpandTableColumn(#"Merged Queries", "Transaction", {"Branch"}, {"Branch"}) in #"Expanded Transaction"
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.
User | Count |
---|---|
106 | |
98 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |