Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alvin199
Helper III
Helper III

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.

alvin199_0-1649124620904.png

 

The sort order is showing this 

alvin199_1-1649124664464.png

 

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. 

alvin199_2-1649124823916.png

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

Pryza91_1-1649134499617.png

 

5) Expand table and select Sort Order:

Pryza91_2-1649134524991.png

 

6) close and apply

7) Sort by column now that you have the sort order in the table.

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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.

Pryza91_0-1649125957996.png

 

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. 

alvin199_0-1649126748344.png

 

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.

 

Anonymous
Not applicable

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. 

Anonymous
Not applicable

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:

Pryza91_0-1649129939835.png

 

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data#:~:text=When%2....

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. 

alvin199_0-1649132712791.png

 

Anonymous
Not applicable

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.

Pryza91_1-1649134499617.png

 

5) Expand table and select Sort Order:

Pryza91_2-1649134524991.png

 

6) close and apply

7) Sort by column now that you have the sort order in the table.

Yup, this works. 

 

Thanks for letting me learn something new now. 

Anonymous
Not applicable

The correct way would be to do it under the Home Tab to Merge Queries, but from the snippet you've sent I can see you don't have a uniquue idnetifier in the List table, you only have years so this isn't feasible.

 

Are you allowed to change the group names? If yes you could use the grouping function of columns which will create bins for you automatically and they are in the correct order (I typed it out based on your example):

Pryza91_0-1649133479193.png

 

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.

alvin199_0-1649134099892.png

 

The issue is due to different data type. In Data View, both Year and Range are in Text data type. 

alvin199_1-1649134362621.png

 

However, the Year in Power Query become Whole number. 

alvin199_2-1649134509776.png

 

Anonymous
Not applicable

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.