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.