Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |