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
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
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?
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.