Forum Discussion
merge multiple rows with condition
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 , 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?
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
but how Receipt Qty became equal to 1274 in PO222? It depends upon backorder qty? It's still unclear.
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.
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!