cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
coathangers
Frequent Visitor

Group by with Text.Combine+List.Transform

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:

ConsoleGame
Nintendo     Animal Crossing
NintendoMario
NintendoZelda
PCCOD
PCGTA
PS4Burnout
PS4COD
PS4Darksouls
PS4GTA
XboxBurnout
XboxCOD
XboxDarksouls
XboxGTA

 

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

 

ConsoleGames
XboxGTA | Darksouls | COD | Burnout
PS4GTA | Darksouls | COD | Burnout
Nintendo     Zelda | Mario | Animal Crossing
PCGTA | COD

 

I want the data to be concatenated alphabetically:

 

ConsoleGames
XboxBurnout | COD | Darksouls | GTA
PS4Burnout | COD | Darksouls | GTA
Nintendo     Animal Crossing | Mario | Zelda
PCCOD | 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 ]

1 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.

View solution in original post

4 REPLIES 4
ChrisMendoza
Super User
Super User

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

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

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

You can also use this:

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

 

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,

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors