Merging a single table to multiple tables at once

[smpa01]

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.

[v-danhe-msft]

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.

https://www.dropbox.com/s/vlpe08m7cuyauxo/Merging%20a%20single%20table%20to%20multiple%20tables%20at...

Regards,

Daniel He

[smpa01]

@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

[v-danhe-msft · Accepted solution]

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.

  https://www.dropbox.com/s/bedqzgi3pmkmzvi/Merging%20a%20single%20table%20to%20multiple%20tables%20at...

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.

[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"