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.
I am having trouble merging queries that have partial data. I am trying to merge Queries B and C with Query A. The end result will create 2 columns for "Sales". I would like "Sales" to be in the same column. Also, I know appending B and C would solve for the sample data, but this will not work for my actual data so let's assume appending is not an option. Here are my sample Queries:
Query A
Name | Week Number |
Jon | 1 |
Jack | 1 |
Hank | 1 |
Jill | 1 |
Jon | 2 |
Jack | 2 |
Hank | 2 |
Jill | 2 |
Query B
Name | Week Number | Sales |
Jon | 1 | 50 |
Jack | 1 | 30 |
Hank | 1 | 60 |
Jill | 1 | 70 |
Query C
Name | Week Number | Sales |
Jon | 2 | 75 |
Jack | 2 | 35 |
Hank | 2 | 65 |
Jill | 2 | 45 |
Actual Result
Name | Week Number | Sales | Sales 1 |
Jon | 1 | 50 | |
Jack | 1 | 30 | |
Hank | 1 | 60 | |
Jill | 1 | 70 | |
Jon | 2 | 75 | |
Jack | 2 | 35 | |
Hank | 2 | 65 | |
Jill | 2 | 45 |
Desired Result
Name | Week Number | Sales |
Jon | 1 | 50 |
Jack | 1 | 30 |
Hank | 1 | 60 |
Jill | 1 | 70 |
Jon | 2 | 75 |
Jack | 2 | 35 |
Hank | 2 | 65 |
Jill | 2 | 45 |
Thank you in advance for your help!
Solved! Go to Solution.
Hi @Anonymous
I'm sure you could write M function to do it, but the easiest solution would be merging all your tables, expanding and later merging the columns together as below.
Why append is not an option?
let Source = Table.NestedJoin(#"Query A", {"Name", "Week Number"}, #"Query B", {"Name", "Week Number"}, "Query B", JoinKind.LeftOuter), #"Expanded Query B" = Table.ExpandTableColumn(Source, "Query B", {"Sales"}, {"Query B.Sales"}), #"Merged Queries" = Table.NestedJoin(#"Expanded Query B", {"Name", "Week Number"}, #"Query C", {"Name", "Week Number"}, "Query C", JoinKind.LeftOuter), #"Expanded Query C" = Table.ExpandTableColumn(#"Merged Queries", "Query C", {"Sales"}, {"Query C.Sales"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Query C", {"Name", "Week Number"}, #"Query D", {"Name", "Week Number"}, "Query D", JoinKind.LeftOuter), #"Expanded Query D" = Table.ExpandTableColumn(#"Merged Queries1", "Query D", {"Sales"}, {"Query D.Sales"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Query D", {{"Query B.Sales", type text}, {"Query C.Sales", type text}, {"Query D.Sales", type text}}, "en-GB"),{"Query B.Sales", "Query C.Sales", "Query D.Sales"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"
Hi @Anonymous
I'm sure you could write M function to do it, but the easiest solution would be merging all your tables, expanding and later merging the columns together as below.
Why append is not an option?
let Source = Table.NestedJoin(#"Query A", {"Name", "Week Number"}, #"Query B", {"Name", "Week Number"}, "Query B", JoinKind.LeftOuter), #"Expanded Query B" = Table.ExpandTableColumn(Source, "Query B", {"Sales"}, {"Query B.Sales"}), #"Merged Queries" = Table.NestedJoin(#"Expanded Query B", {"Name", "Week Number"}, #"Query C", {"Name", "Week Number"}, "Query C", JoinKind.LeftOuter), #"Expanded Query C" = Table.ExpandTableColumn(#"Merged Queries", "Query C", {"Sales"}, {"Query C.Sales"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Query C", {"Name", "Week Number"}, #"Query D", {"Name", "Week Number"}, "Query D", JoinKind.LeftOuter), #"Expanded Query D" = Table.ExpandTableColumn(#"Merged Queries1", "Query D", {"Sales"}, {"Query D.Sales"}), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Query D", {{"Query B.Sales", type text}, {"Query C.Sales", type text}, {"Query D.Sales", type text}}, "en-GB"),{"Query B.Sales", "Query C.Sales", "Query D.Sales"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |