Ranking in Power Query via multiple columns

I'm looking to rank the top 5 highest values per company in a list preferably in power query. I have a list of companies in a column (col A of the attached) and then an amount (col B of the attached). I've already used PQ to sort the companies alphabetically and the amounts in descending order. The table is dynamic and there's no way for me know how many values will exist for Company A, B or C each time I run the report or when companies will be added over time. I'm looking to create a new table that only retains the top 5 highest values per company. Duplicate values within the company should still count up so I only have a max value of 5 rows per company.

Once I can figure out how to rank these properly I can just filter out anything that is not a top 5 and continue on with the query. Appreciate your help.

Does the attached do it for you?
The PQ doesn't require any sorting beforehand.
I've ignored your C column.

What do you want to happen if there are more than 5 similar amounts for a company's top performers? At the moment it's pretty random which 5 get included.

Thank you!

I don't know the answer to your question at this point, I'll need to get with the report owner to determine if there's a high level of relevance for which one stays vs. which one goes. I do have several other columns in the master report one of which is days aged, he may want the oldest item to stay.

I've never used custom functions before so this will be fun to try to plug in and see how it goes. Thanks for your help! I'll come back to this thread if I gain additional info that could be added to the function.

Try in C2:

Please Login or Register  to view this content.

Thanks Soledad, but I was looking to do this in PQ vs. formula.

This worked perfectly and so fast! Thank you