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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.