Forum Discussion
Merging a single table to multiple tables at once
Hi everyone,
Is it possible for PQWRY to mege one table to multiple tables at once.
The sample data is here- https://drive.google.com/open?id=1fBe0MNgoVnpspiZJwzQJHMhzlS1nYqpP
I want to know if it is possible at all to Merge ID column of MASTERID table to RAWDATA1 and RAWDATA2 ID column at once to reach to the DESIRED table. The objective of this task is to find out if there is a capacity exists withing PQWRY that eliminates the need to merge queries repeatedly to reach to the "DESIRED". The easy alternative #1 is definitley repeated Merging Queries followed by applying Logical Operators to come the "Desired" which is difficult to do when the number of RAWDATA table increases. The easy alternative #2 is Appending all the RAWDATA to a single table which is not also an option as each of the RAWDATA tables has a lot of steps that make the APPENDING a long time consuming task.
I tried doing the following which offcourse did not work and I was wondering if anyone has a trick up their sleeve to achieve what I want.
Thank you in advance.
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
5 Replies
Hi 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
v-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
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
