2 Ways to Rank With Ties Using Power Query

Rank with Ties using Power Query

Recently I worked on a challenge from Ken Puls, where I needed to figure out how to Rank With Ties. Ken had a set of data containing items and sales. And his idea was to plot the performance of the different items in a graph. As there were many items, this could become overwhelming to plot in a single graph. To avoid that Ken wanted to specify the Top 5 performers and categorize the remaining items in a category called “others”. By doing this you would have a focus on only the major sellers. So far so good?

The challenge is that the used data set, ended up with the number 5 and 6 having the same sales value. In that case the numbers 5 and 6 should both be included in the ranking in Power Query. Yet Power Query does not have a native function to handle ties. So how would you generate a ranking of the top 5 that includes ties? I sent in two videos that you can find below:

Rank With Ties Using Earlier Steps (Video Tutorial):

The first method makes use of several steps. The key to find the top 5 including ties from this method lies in referring to earlier steps.

Rank With Ties Using Group By (video tutorial):

Later I came up with another way to rank the data with ties. This method uses the Group By functionality. Compared to the previous method, this is an easier (and the recommended) way of ranking your data with ties.

I hope these videos gave you some new insights on how you can rank your data with ties using Power Query. For more details on how to group data, check out this post. And if you have a good solution to this challenge, please share it in the comments!

Share on:
About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a comment