merge multiple rows with condition

[dogburalHK82]

Hi, 

I have a raw table from ERP and they are multiple rows having the same PO no. as well as part number (or PO line no.). 

Now I would like to merge those into one single row, I wonder how I can acheive.

Below what I would like to have at the end.

When you look at PO222, it has multiple order lines. Especially for PDN-D and PDN-F, we have a multiple lines showing partial delivery. 

For PDN-D, intially Quantity ordered was 1200 then increased to 1274.

And the order was complete as backorder quantity is zero.

For PDN-F, the initial Quanity remained the same then order is completed in two separate deliveries. 

Regards

mk

[AlienSx · Accepted solution]

Hello, @dogburalHK82 if you need the very last record per each PO and item then try this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    idx = Table.AddIndexColumn(Source, "idx", 1, 1, Int64.Type),
    f = (t as table) as record => Record.RemoveFields(Table.Last(Table.Sort(t, "idx")), "idx"),
    group = Table.Group(idx, {"Purchase Order", "Item Number"}, {{"all", each f(_), type table [idx=number]}}),
    z = Table.FromRecords(group[all])
in
    z

[dogburalHK82]

@AlienSx thank you

Would you be able to share the file?

[AlienSx]

sorry, I don't know how to attach Excel file. All you need to do is

 - make sure that your source table name is Table1

 - launch power query editor

 - create new blank query, open Advanced Editor and replace everything inside with the code above

[dogburalHK82]

@AlienSx 

Thanks for that, 

However, for example for PO222, since backorder is 0, meaning received all 1274, hence receipted qty should be also 1274. 

With condition of Backorder, I also need to select the last receipted date.

How can I put like "IF" condition when grouping rows?

[AlienSx]

@dogburalHK82 , please explain how to get the row from the table with the same PO and Item. E.g. receipt date should me max date in corresponding column (receipt date). Qty ordered - the one when receipt date = max? What about other columns? 

[dogburalHK82]

@AlienSx 

for receipt qty, it should be maximum

for receipt date, it should be maximum

for quantity ordered, should be maximum

for backorder quantity, the number to be used should be taken from the row of the latest receipt date 

[AlienSx]

but how Receipt Qty became equal to 1274 in PO222? It depends upon backorder qty? It's still unclear. 

[dogburalHK82]

@AlienSx 

That is becase we had partially received the parts in the previous month. For this case, we need to calculate the total receipted qty based on backorder quanty and qty ordered. 

[AlienSx]

@dogburalHK82 sorry, I still don't see any logic behind your sample data. Lets take single line items as an example: 

PND-A: 1778 // 5 // 20 => 1778 // 1758 // 20 (so that delivered = ordered - backorder)

PND-B and C work the same way. But this is not true for PND-E:

80 // 6 // 15 => 80 // 6 // 15. I just can't find any logic behind this. 

Take a look at PND-F as well: zero backorder but receipt qty is still 12 (I would expect 180).

I am sorry, I give up. You can always modify "f" function to work the way you want. The key is to group your data by PO and item number w/o any aggregation. Index column is probably not required as well - it serves as sorting column (Table.Group may shuffle data). Good luck!   

[dogburalHK82]

@AlienSx They are some odd cases where while waiting for the backorder, the qty gets increased. Backorder being zero but receipted only 10 in this month is because we most likely received some in the previous month. Anyways, thanks for your help.