Forum Discussion
Ranking a text category column
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!
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.
3 Replies
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 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.