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
Anonymous
Not applicable

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 AppointedRank
<21
10+3
5 to <102

 

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!

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@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 YouTube.gif 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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@Anonymous glad to hear it worked out. Cheers!

 

Follow us on LinkedIn and YouTube.gif 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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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 YouTube.gif 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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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.

Top Solution Authors