Merging Table Duplicates Rows

[mussaenda]

Above photo is the Sales Table and the Shipment Table.

The Lower part of the photo is what happens when I merge vs What I need when I merge.

The qty of order is duplicating its value and I  don't know how to deal with it. I tried reading other topics related to this but it does not answer my problem. The qty of order must not aggregate. Is there a way to do it?

Thank you,

Mussaenda

[v-jiascu-msft]

Hi Mussaenda,

Actually, we establish a relationship between two tables based on [Job Number] in this scenario. You almost can get everything you want. If you really need to do it like that, I'm afraid you have to remove the other two values manually. But there could be a problem. There are 9 orders in your desired result which are 3 orders with QTY while 6 orders with 0 QTY.

Best Regards,

[mussaenda]

Thank you for your response, @v-jiascu-msft.

Your explanation helped me to understand this better. Manually removing the other two values will take a lot of time since we have the data from the year 2011 up to present.

The scenario is, when we received an order, it is delivered partially based on when the customer needs it. That is why the QTY of orders must not duplicate. We are doing this to track the QTY that are not yet shipped by week.

[Stachu · Accepted solution]

you could try with appending the Shipment table to sales and then then sorting by job and date, and filling down e.g. like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0MLJR0lQ0MLAwMDQxDLwEApVgcobYQhbQRkmUKljTF1GwNZxiDpWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, #"Job Number" = _t, #"Qty of Order" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Job Number", Int64.Type}, {"Qty of Order", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Shipment}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Job Number", Order.Ascending}, {"Order Date", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Order Date"})
in
    #"Filled Down"

that would work as long as each order has only one Order date

[mussaenda]

Thank you @Stachu, you are getting me closer to what I really needed.

I have few questions to ask though. If it's okay.

Thank you,

Mussaenda

[Stachu]

have you tried appending the tables? it would create something like this:

with proper order sequence you could then use fill down to fill in Order dates

or you could add a temporary index for each job and merge on Job number and the index

In Orders each job would have Index 1, and in Shipments only first shipment would have index 1, the rest woudl be increments, therefore avoiding the duplication