Answered by:
Group data and sort rows inside group
Question

Dear All,
In Power Query I am looking for an elegant solution to group the first three columns of my dataset (including all rows) and inside this grouped data sort by timestamp and another column called order. After that I would like to pick the first row of the grouped rows. Anybody a good solution for this in powerquery?
Thanks Michiel Soede
Answers

Hi Michiel,
You need to add three steps to your query, e.g.
GroupedRows = Table.Group( <PreviousStepName>, {"Column1", "Column2", "Column3"}, {"Tables", each _, type table} ), SortedRows = Table.TransformColumns( GroupedRows, { "Tables", each Table.Sort(_, {"timestamp", "order"}) } ), FirstRows = Table.TransformColumns( SortedRows, { "Tables", each Table.First(_) } )
 Proposed as answer by Colin Banfield Sunday, February 3, 2019 6:24 PM
 Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM

Glad that the solution worked out. I meant to include the default ordering, so you could see where it needed changing if you didn't want to order by ascending values, but you figured it out!
You could also combine the sort operation with the group step:
GroupedRows = Table.Group( <PreviousStepName>, {"Column1", "Column2", "Column3"}, {"Tables", each Table.Sort(_, {{"timestamp", Order.Descending}, "order"}), type table} ), FirstRows = Table.TransformColumns( GroupedRows, { "Tables", each Table.First(_) } )
 Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM

Well  each Table.First(_) is the same as each (_){0}. So the second row would be each (_){1}. If you want the first two rows, then each Table.FirstN(_, 2).
 Marked as answer by Michiel Soede Monday, February 4, 2019 12:48 PM
All replies

Hi Michiel,
You need to add three steps to your query, e.g.
GroupedRows = Table.Group( <PreviousStepName>, {"Column1", "Column2", "Column3"}, {"Tables", each _, type table} ), SortedRows = Table.TransformColumns( GroupedRows, { "Tables", each Table.Sort(_, {"timestamp", "order"}) } ), FirstRows = Table.TransformColumns( SortedRows, { "Tables", each Table.First(_) } )
 Proposed as answer by Colin Banfield Sunday, February 3, 2019 6:24 PM
 Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM

It is really astonishing what you can reach with so little code. This works like a charm. Many thanks for this solution.
Only thing I added to it was the sort order for the timestamp (where I needed the most recent record):
SortedRows = Table.TransformColumns(GroupedRows, {"Tables", each Table.Sort(_, {{"Timestamp",Order.Descending}, "Order"})}),
Regards!
Michiel

Glad that the solution worked out. I meant to include the default ordering, so you could see where it needed changing if you didn't want to order by ascending values, but you figured it out!
You could also combine the sort operation with the group step:
GroupedRows = Table.Group( <PreviousStepName>, {"Column1", "Column2", "Column3"}, {"Tables", each Table.Sort(_, {{"timestamp", Order.Descending}, "order"}), type table} ), FirstRows = Table.TransformColumns( GroupedRows, { "Tables", each Table.First(_) } )
 Marked as answer by Michiel Soede Monday, February 4, 2019 10:49 AM





Well  each Table.First(_) is the same as each (_){0}. So the second row would be each (_){1}. If you want the first two rows, then each Table.FirstN(_, 2).
 Marked as answer by Michiel Soede Monday, February 4, 2019 12:48 PM
