Forum Discussion

dogburalHK82's avatar
dogburalHK82
Helper III
3 years ago
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 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

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

10 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    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
      • AlienSx's avatar
        AlienSx
        Super User
        3 years ago

        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