Forum Discussion
Sorting on Grouped Column
This took me a few minutes now that I have some sample data to work with. It brute forces it (and by no means do I recommend this) but it maintains the column headers etc that you currently have:
1) Keep Year as a whole number
2)Create a custom column in the List table with the following code. This will make bins for you:
if [Year] <= 5 then "<5" else if [Year] >5 and [Year] <=10 then "6-10" else if [Year] >10 and [Year] <= 15 then "11-15" else if [Year] >15 and [Year] <=20 then "16-20" else "21-25"
3) Home Tab > Merge Queries
4) Merge List table and Sort Range Table using Bin in the List table, and Range in the Sort Range Table.
5) Expand table and select Sort Order:
6) close and apply
7) Sort by column now that you have the sort order in the table.
Hi Anonymous ,
The Sort by Column function does appear in the ribbon.
When I sorted by Year (Group), the columns in the matrix table (show in the first post) still remain the same. It will not sort into <5, 6-10, 11-15, 16-20 and 21-25.
That looks like you may have selected the Country column in the Fields pane instead of the Year (Groups) column.
If I'm wrong, it may be that your "Sort Order" column is not present in the table where your Year (groups) column is, in which case you will want to bring it into that table so you can sort by it.
I would like to bring in a sort order table but by append. However, I cannot use append because the Group column is not available in Power Query.
This would be a non-standard recommendation for other scenarios because it is technically not a unique identifier (like an auto-incremented ID), but you could use Merge in transformation and do a left outer join using the Range column as the unique identifier to bring the Sort Order in. This will bring the column into the table. Once that's occurred you can apply settings, and then sort column using the Sort Order.
From the Microsoft Documentation:
In Power Query, the Group year range is unavailable and the Merge function is grey out in Transformation menu. Thus, it is impossible to do unless I have missunderstand it.