Forum Discussion
Sorting on Grouped Column
I have 2 column country name and number of year for each row. Next, I change the number of year to Text to group then into <5, 6-10, 11-15, 16-20 and 21-25.
I put the groupping into column of a matrix table but I am unable to sort the column in ascending order.
The sort order is showing this
I have tried to create a new table like this and wanted to append it into the original table and use the Sort by Column in Data View. However, the grouped range does not shown in Power Query for me to do the append operation.
Thus, what should I try to sort the group column in ascending order?
Here is the sample data that I have tried.
https://drive.google.com/file/d/1knjW26Qo0WbqoL8O9e7vIVmwFs8wtuuK/view?usp=sharing
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.
11 Replies
With the Column selected in your Fields pane, do you have Column Tools appear in the Ribbon?
If you do, can you try to use the sorting function (Sort By Column) which should enable you to sort the column by your ranking column.
Column Tools > Sort By Column > Select column "Sort Order".
This should then put the columns into the correct order you are wanting.
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.
