2 Ways to 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

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

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.

Conclusion

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!