Group by with Text.Combine+List.Transform

[coathangers]

Hi,

I am trying to concatenate rows using Tables.Group and Text.Combine+List.Transform. I want to sort the order so that the rows are concatenated alphabetically (Power Bi is doing this reverse alphabetical order).

Example data:

Console	Game
Nintendo	Animal Crossing
Nintendo	Mario
Nintendo	Zelda
PC	COD
PC	GTA
PS4	Burnout
PS4	COD
PS4	Darksouls
PS4	GTA
Xbox	Burnout
Xbox	COD
Xbox	Darksouls
Xbox	GTA

When I use Text.Combine+List.Transform it is returning this:

Console	Games
Xbox	GTA | Darksouls | COD | Burnout
PS4	GTA | Darksouls | COD | Burnout
Nintendo	Zelda | Mario | Animal Crossing
PC	GTA | COD

I want the data to be concatenated alphabetically:

Console	Games
Xbox	Burnout | COD | Darksouls | GTA
PS4	Burnout | COD | Darksouls | GTA
Nintendo	Animal Crossing | Mario | Zelda
PC	COD | GTA

I am using this code (adapted for the above example):

= Table.Group(#"Previous Step", {"Console"}, {{"Games", each Text.Combine(List.Transform([Game], Text.From), " | "), type text}})

I have tried using an order, but it has no effect:

= Table.Group(#"Previous Step", {"Console"}, {{"Games", each Text.Combine(List.Transform([Game], Text.From), " | "), type text, Order.Ascending}})

The sort order of the query from previous steps has no effect on the Table.Group query.

Any suggestions please? 

Many thanks

Colin

[Edited to include missing { , thanks @ChrisMendoza ]

[ChrisMendoza]

@coathangers - Only difference I see is an opening " { ".

= Table.Group(#"Changed Type", {"Console"}, {{"Games", each Text.Combine(List.Transform([Game], Text.From), " | "), type text, Order.Ascending}})

[coathangers · Accepted solution]

I found the solution here:

PowerQuery order of words in Text.Combine - Stack Overflow

We first need to override the Power BI internal sort using Table.Buffer and then sort by Console ascending and Game ascending.

The Text.Combine has no means of sorting, this Table.Buffer needs to be a previous step in the query.

[Anonymous]

You can also use this:

= Table.Group(#"Changed Type", {"Console"}, {{"Games", each Text.Combine(List.Sort([Game]), " | " ), type text}})

[coathangers]

Thanks for the reply. 

Sorry that code was typed rather than copied from the source and I forgot the {. The source data I have is sensitive so can't copy directly. 

Even with that opening { (which is how the code in my query actually is) the Order.Ascending has no effect, the concatentation is in reverse alphabetical order for some reason. Did you try Order.Descending in your query, and did that have any effect?

Many thanks,