Power Query - Group by MAX Column Value-CodePudding

I have a transactional table that shows me multiple records per order.

Now I woule like to get per Order the Data, Value and Amount based on the MAX in Column Key. Hiw is this possible by using Power Query? The result would be like that:

CodePudding user response：

You can create a partition (group) per Order, sort the partition by Key descending, keep the first row, then combine those rows:

let
    Partitions = Table.Group(MyTable, {"Order"}, {{"Data", each Table.FirstN(Table.Sort(_,{{"Key", Order.Descending}}),1), type table}}),
    Combined = Table.Combine(Partitions[Data])
in
    Combined

CodePudding user response：

CT = Table.TransformColumnTypes(
    Source, 
    {
      {"Order", Int64.Type}, 
      {"Date", type date}, 
      {"Value", Int64.Type}, 
      {"Amount", Int64.Type}, 
      {"Key", type number}
    }
  ), 
  #"Filtered Rows" = Table.SelectRows(
    CT, 
    each ([Key] = List.Max(Table.SelectRows(CT, (q) => q[Order] = [Order])[Key]))
  )