preserve order when using Table.Group

[xieofxie]

As documented, Table.Group "cannot guarantee to return a fixed order of rows". While this is bad for sorted data when used with Text.Combine. For example, the following is sorted by Time first and I would like to group by Id while Combine Text in order.

Id	Time	Text
1	12:00	abc
1	13:00	def
2	14:00	ghi

Another approach would be to create a new column with Time and Text as Table first, then save it as list when grouping, and finally reorder the list according to Time. But it is too complex.

Any ideas? Thanks~

[Greg_Deckler]

If it is complex M, then it's @ImkeF 

[ImkeF · Accepted solution]

You can try GroupKind.Local - to my understanding, it should keep the sort order (but you can only use it if your data is in a consecutive order: https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/ 

[xieofxie]

Thanks~ But how could we make sure of that (keep order)? I didn't find any document about GroupKind.Local.

[v-eachen-msft]

Hi @xieofxie ,

The GroupKind.Local optional parameter is the key to getting the behaviour you can see here. The default type of grouping, GroupKind.Global, does a standard group by across the whole table as seen in the first example above. 

You can refer to this document:

https://blog.crossjoin.co.uk/2014/01/03/aggregating-by-local-groups-in-power-query/