CODEWITHSUNDEEP
excelpowerbipowerquery
Aaron
Aaron

Reputation: 331

Power Query - Group by MAX Column Value

I have a transactional table that shows me multiple records per order. enter image description here

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:

enter image description here

Upvotes: 0

Views: 1582

Answers (2)

smpa01
smpa01

Reputation: 4282

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]))
  )

Upvotes: 0

Olly
Olly

Reputation: 7891

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

Upvotes: 1

Related Questions