Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merge issues

Hello,    I can't seem to figure this merge thing out.   I have cleaned, trimmed, captialized, watched videos from everyone...and still I can't seem to get the correct results.    In the end, after...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi Anonymous,

     

    see the sample pbix below which merges the data you provided

    sample.pbix 

     

    Steps to reproduce

     

    -> Import both data tables Orders and Shipping

    ->Click on the table icon left hand side of the header row on the shipping table

    --> select Merge Queries

     

     

    -> Select the Orders Table as the second Table

    -> Hold down the shift or ctrl and select the Order | Line No | Product Column as the Join Columns for both Tables

    --> Default Left Outer Join should provide what you are looking for

    -> Click  OK

     

    -> You will see a new column in the shipping table of type table

    -> Click the Expand Icon next to the Header Orders

    -> Only Select the Promo Column and uncheck the Use Original Column as prefix option

    -> Click OK

     

     

     

     

    And you're done

     

    Hope this helps


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

     

BA_Pete's avatar
BA_Pete
Super User
6 years ago

Hi Anonymous ,

 

You can join (merge) on multiple columns at once by holding down the Ctrl key and selecting a second/third/fourth etc. column. you need to match the join columns AND THE ORDER OF SELECTION on the second table too, but it is doable.

 

I've mocked up how I think your scenario looks based on your test data and used this technique to join as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKNlTSUTI0MjYBUsamSrE60UpJyYnGQB5QzBRImUAE09NSzcA8M3MgZWoGFsxKKwLpMzI0BcmZWyjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [promoCode = _t, order = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"promoCode", type text}, {"order", type text}, {"value", Int64.Type}})
in
    #"Changed Type"

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. Call this query "aTable".

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUTI2VYrViVYCckyBPBMIz8TUzBzIMzWDyBkCGTpK5hZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"order", "value"}, aTable, {"order", "value"}, "aTable", JoinKind.LeftOuter),
    #"Expanded aTable" = Table.ExpandTableColumn(#"Merged Queries", "aTable", {"promoCode"}, {"promoCode"})
in
    #"Expanded aTable"

Do the same again to add this into your Power Query. Here you will see the steps I took to merge your first table onto your second to get PromoCode joined. I multi-selected Order and Value on both aTable and bTable to get this join.

Be aware that this method of joining may cause some unwanted effects if you happen to have two orders that have the same Order Number and Value (however unlikely this is).

 

Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    BA_Pete   Pete - thanks for this...I will dive in later today!