Merge issues

[Anonymous]

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 the merge, I need the total $ sum of all orders to be the same as they were originally...not more...not less.   I simply need the column (in this case Promo Code) to be appended (column, not row) to the main table.     What is happening is I am either getting a $ sum value that is higher...or lower...than my original value.  

Simplisticly, what I am trying to achieve is this:  

Table 1                                                      Table 2    (contains Order  and   Value....but not Promo Code...I want to add Promo Code)

Promo Code    Order    Value

abc1                 1234       $35

bca3                 2345       $45

gfe6                 4567        $56

I want to merge my original table  (Table 2) with a table called Promo Codes  (which is actually extracted from the Orders table).   In my case, the Orders table contains less records than the Shipments table.    I extract the Promo Codes column, clean, trim, etc....and then merge with my Shipments table   (because I need the Promo Codes to end up in my shipping table...matched to the same orders they were matched to in the Orders table).   And yes, there can be the same promo code tied to one order twice  (because it could be tied to different lines on the order).   

[richbenmintz · Accepted solution]

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!

[Anonymous]

@BA_Pete @amitchandak @richbenmintz       All   -  Thank you for the help.    In the end, Rich was able to use my recreated example to show me just how simple this actually was.   Although the data I gave him was very clean (no trimming, de-dupe, etc needed), it was evident right away what I was doing wrong. 

I was not paying attention to the which table I put first (or "left").    I was putting my Order table first, not my Shipments table, and that seemed to be the cause of my issues.   I have since successfully "merged" two additional columns from my Orders table to my Shipments table with perfect results!  

THANK YOU!   

[BA_Pete]

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]

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

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?

Hope you have already explored

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

@amitchandak    Hi Amit  -  Yes, I have indeed watched those...and your's and other's tips have been helpful.  But still can't seem to get there.   

I think I am at the point where I need to try and put together some "example" data and upload.  I have a huge model, with company info in it, so need to spend some time to recreate a simplistic version of the issue that can be shared, which I will do, and upload in this thread. 

[amitchandak]

@Anonymous , what you pasted seem like text, not a table. So not able to get much out of it.

[Anonymous]

@amitchandak     Attached is a simple file (with two tabs).     Goal is to get the Promo field into the shipments table.

https://drive.google.com/file/d/1po5DMFcwQR54CpfmsuyxUKcDtRXwB20j/view?usp=sharing

Note there is one less row on the shipment table than on the order table...normal because we have orders that have not yet shipped.