Forum Discussion
Community ChampionMerging a single table to multiple tables at once
Hi, smpa01
Based on my test, it’s not able to merge three tables in one step with Merge Queries feature, but we can use nested Table.NestedJoin function in Power Query to do it. You can refer to below steps in query editor:
1.Open the “Advanced Editor” of the ‘MASTEDID’ table and enter the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
#"Merge three tables" = Table.CombineColumns(Table.ExpandTableColumn(Table.ExpandTableColumn(Table.NestedJoin(Table.NestedJoin(#"Changed Type",{"ID"},RAWDATA1,{"ID"},"RAWDATA1",JoinKind.LeftOuter),{"ID"},RAWDATA2,{"ID"},"RAWDATA2",JoinKind.LeftOuter), "RAWDATA1", {"Name"}, {"RAWDATA1.Name"}),
"RAWDATA2", {"Name"}, {"RAWDATA2.Name"}),{"RAWDATA1.Name", "RAWDATA2.Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merge three tables"
2.Click the “Done” and you can see the correct result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Microsoft EmployeeHi, smpa01
Based on my test, it’s not able to merge three tables in one step with Merge Queries feature, but we can use nested Table.NestedJoin function in Power Query to do it. You can refer to below steps in query editor:
1.Open the “Advanced Editor” of the ‘MASTEDID’ table and enter the code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
#"Merge three tables" = Table.CombineColumns(Table.ExpandTableColumn(Table.ExpandTableColumn(Table.NestedJoin(Table.NestedJoin(#"Changed Type",{"ID"},RAWDATA1,{"ID"},"RAWDATA1",JoinKind.LeftOuter),{"ID"},RAWDATA2,{"ID"},"RAWDATA2",JoinKind.LeftOuter), "RAWDATA1", {"Name"}, {"RAWDATA1.Name"}),
"RAWDATA2", {"Name"}, {"RAWDATA2.Name"}),{"RAWDATA1.Name", "RAWDATA2.Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merge three tables"
2.Click the “Done” and you can see the correct result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
Community Championv-danhe-msftThanks Daniel for the reply. I have not had the time to go through the solution yet. I will get back to you soone. Thans for your time.
- smpa01
v-danhe-msftI have tested the code and it works for me. It is a nice work around to "at once". You are nesting multiple mergings at once and it would save me some time. I had to disect your code by parts to understand and hence, the delay.
Thanks for the help mate !!! Below is your code broken down in parts
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(Table.NestedJoin(#"Changed Type",{"ID"},RAWDATA1,{"ID"},"RAWDATA1",JoinKind.LeftOuter),{"ID"},RAWDATA2,{"ID"},"RAWDATA2",JoinKind.LeftOuter), #"Expanded RAWDATA1" = Table.ExpandTableColumn(#"Merged Queries", "RAWDATA1", {"Name"}, {"Name"}), #"Expanded RAWDATA2" = Table.ExpandTableColumn(#"Expanded RAWDATA1", "RAWDATA2", {"Name"}, {"Name.1"}), #"Merged Columns" = Table.CombineColumns(#"Expanded RAWDATA2",{"Name", "Name.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"