Please note: the submission period for the Top x with Ties Challenge is now closed. You can check out the follow-up post where the results are summarized here.

Yesterday I was working on a data set, and needed to work out the Top x with Ties in Power Query.  This posed to be a bit more challenging than I first thought it would be.

Why did I want Top x With Ties?

Let's take a look at the source data here, on a simplified data set.  That data looks (in part) like this:

I needed to chart it, and wanted it to look like this:

Now, appreciating you can't see all the data, the deal is this… I wanted to add a column to the data set that shows the item name for the top 5 items, and shows "Other" for all the rest.  This allows me to group things to show how the major sellers compare to the rest of the business.  Make sense so far?

The trick here is that I ended up with two item (Pint Winter Ale and Caesar) that have exactly the same value, which meant that I needed 6 categories in this case.  But Power Query doesn't have a native function to keep the Top x with Ties, it only has Keep Top Rows.

So how would you generate the Top x with Ties for this scenario?

Basically, what I need in order to serve up the chart correctly is this:

So here's the criteria… we want it to:

• Automatically provide the Top x with Ties
• Be easy to update to change the value of x (in case we want top 10)

Now you might think "Hey that's easy... I'll just sort it, add an Index column and then I can use a conditional column to choose anything over a certain number."  That's perfect if you're not worried about ties, but in this case we want the Top x WITH Ties.  So now what?  Can you filter to keep the Top x rows?  Nope, because again, that only keeps those rows, and not the ties.

Now I have two solutions for this already, but I'm curious how you'd approach this challenge.

Just to make this more fun…

Do NOT post your answer below.  (We don't want to spoil it for anyone.)

Instead, email your workbook to Rebekah at (you know) Excelguru dot ca.  We'll collect them and share the best ones (along with mine) next week.

You can download the source data here.  Let's see what you've got!