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,
I want to know the meaning of “at once” you mentioned in your problem. If you want to append two columns and merge another column by one step, you can refer to below steps in query editor:
1.Click the Table ‘RAWDATA1’ and open the “Advanced Editor” function.
2.Add the sentence #"Desired" = Table.RemoveColumns(Table.NestedJoin(Table.Combine({#"Changed Type", RAWDATA2}),{"ID"},MASTEDID,{"ID"},"MASTEDID",JoinKind.LeftOuter),{"MASTEDID"})
before the “in” and replace the sentence #"Changed Type" with the #"Desired" like the picture below.
Now you can see the result.
You can also download the PBIX file to have a view.
Regards,
Daniel He
smpa01v-danhe-msftthanks for your reply.
"I want to know the meaning of “at once” you mentioned in your problem" -
"at once" means
merging MASTERID table to "RAWDATA1" and "RAWDATA2" in a single step
instead of
merging MASTERID table firstly to "RAWDATA1" and finally to "RAWDATA2" in two (2) different steps
The solution that you provided is not the one I am looking for. Perhaps I did not explain my problem in great lenth and that's why it was perceived differently by you.
You have simply appended RAWDATA1 and RAWDATA2 to come to the "DESIRED" which is not what I want. I want MASTERID to be merged to "RAWDATA1" and "RAWDATA2" at once to come to the "DESIRED".
Please take a look at the modified MASTERID table which needs to be Merged to RAWDATA1 and RAWDATA2 at once (if possible) to come to modified "DESIRED".
MASTERIDDESIRED
To give you a better illustration presently I am doing this
Merging 1
and this after merging 1
Merging 2
instead I want the following at once (if possible in a single Merging Queries step)
DESIRED
- v-danhe-msft
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
- smpa01
v-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.