Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Resident Rockstar
Resident Rockstar

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.