Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
8 years ago
Solved

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 k...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    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%20once2.pbix?dl=0

     

    Regards,

    Daniel He

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
smpa01
Icon for Community Champion rankCommunity Champion
8 years ago

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"