Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 ]
Solved! Go to 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.
@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}})
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,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |