Keep sorted table for Group By, using Table.Buffer

Let´s say you have a table like this:

You want to group the animals by group, so you use M-code like this one:

• let
     Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
     SortedRows = Table.Sort(Source,{ {“Group”, Order.Ascending},{“Something”, Order.Ascending}}),
     GroupedRows = Table.Group(SortedRows, {“Group”}, {{“Merged”, each Text.Combine([Something],”, “), type nullable text}})
  in
     GroupedRows

And, surprisingly, this is the result:

The surprising thing is that although it is grouped correctly, the grouped items are not sorted in ascending order, even though you have explicitly used a Sort Rows step. This is shamelessly ignored by Power Query.

How to solve it?

Just add a step that loads the sorted table into memory, and continue working with the sorted table:

• let
     Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
     SortedRows = Table.Sort(Source,{ {“Group”, Order.Ascending},{“Something”, Order.Ascending}}),
     Buffer = Table.Buffer(SortedRows),
     GroupedRows = Table.Group(Buffer, {“Group”}, {{“Merged”, each Text.Combine([Something],”, “), type nullable text}})
  in
     GroupedRows

Now you have the result you wanted:

Btw, if you don’t want to buffer the table (sometimes it can slow you down), you can also use sorting directly in the grouping:

• let
     Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
     SortedRows = Table.Sort(Source,{ {“Group”, Order.Ascending},{“Something”, Order.Ascending}}),
     GroupedRows = Table.Group(SortedRows, {“Group”}, {{“Merged”, each Text.Combine(List.Sort([Something]),”, “), type nullable text}})
  in
     GroupedRows

The result is identical as before.