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 again,
I ma allow to change the group name but it is great if it can be stated from x to y so the reader can know the limit of the range.
I am still stuck with the Merge part. I have created a new table called Sort Range. However, there are some issue as I am unable to click on OK button to proceed.
The issue is due to different data type. In Data View, both Year and Range are in Text data type.
However, the Year in Power Query become Whole number.
When merging it needs to be on unique Identifiers which I only realised you don't have common unique ID's when I saw the snippet of the 2 tables you have.
However hopefully my answer before (involving creating a custom column) will help you achieve what you need.