Power Query: Row Together Part 4
16 July 2025
Welcome to our Power Query blog. This week, I look at how to keep a group in order.
Over the last few weeks, I have been looking at how to solve tasks involving the manipulation of data to form new rows. When I was grouping the data I was reminded of an issue and a trick that I haven't covered in this blog series. Let's start with the SalesRegionsColumns query I created in Part 2. I have filtered on Region to remove region data that is blank or null.
The task is to create a row for each Salesperson containing all the regions, but with the regions in alphabetical order.
I will use the same approach I used in Part 1. First, I sort the data in alphabetical order by Region.
Next, I group the regions by 'summing' them for each Salesperson:
I change the M code from:
= Table.Group(#"Sorted Rows", {"Salesperson"}, {{"Regions", each List.Sum([Region]), type nullable text}})
to:
= Table.Group(#"Sorted Rows", {"Salesperson"}, {{"Regions", each Text.Combine([Region],", "), type nullable text}})
I check the results:
They are not in alphabetical order! Now for the trick. Insert a step before the 'Grouped Rows1' step by selecting to view the 'Sorted Rows' step and then choosing to add an 'Index Column' from the 'Add Column' tab:
Ignore the warning that you are inserting a step and choose to Insert.
This creates an Index column.
Go back to step 'Grouped Rows1' and view the data:
The Regions data is now in alphabetical order. You can delete the Index column, and your data is in the correct order. The reason is not obvious and probably has something to do with the language used to create M code. I think you'll agree it is a useful trick to know about.
Come back next time for more ways to use Power Query!
