Ranking a text category column

[Anonymous]

Hello eveyone,

I'm having trouble finding a solution to a problem I'm facing. I have a column called years_appointed which categories based on a year range. I'd like to be able to have the data sort desc but because it is a text value it sorts incorrectly, so I need another column that attaches a numeric value to be able to sort. However, when I create a new column using a switch statement to create a numeric value to be able to sort I get a circular dependency issue.

What I have:

Years Appointed

<2

10+

5 to <10

I'd like to be able to use the years appointed column and have it display in the correct order. At the moment, if I sort based on Years Appointed it doesn't sort properly.

What I tried, but got a circular dependency when I tried to sort by rank on the years appointed column.

Years Appointed	Rank
<2	1
10+	3
5 to <10	2

What I want to display:

Years Appointed

<2

5 to <10

10+

Any help is appreciated, thank you so much in advance for your expertise and time!

[parry2k]

@Anonymous glad to hear it worked out. Cheers!

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@Anonymous if you added a RANK column based on Years, you can use the rank column on the sort by for Years. I would recommend adding rank column in PQ using the new function introduced recently Rank column (Preview) - Power Query | Microsoft Learn

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

Thank you so much for your help. I didn't see the option to add a rank column, but I did choose to use PQ to add a rank conditional column. When grouping by rank on years appointed, I was able to successfully sort! 

TY!