Forum Discussion
Solved
merge multiple rows with condition
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 c...
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
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
zAlienSx thank you
Would you be able to share the file?
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?
