How to Rank in Power Query. EMT 1698 by excelisfun.

Learn how to rank in Power Query. Power Query Steps and M Code works in Excel or Power BI.
Topics:
1. (00:00) Introduction.
2. (00:23) Load data.
3. (00:38) Group By Product to get Total Sales
4. (01:06) Sort Total Sales
5. (01:14) Add Index Column
6. (01:29) Group By Total Sales, but make MIN aggregate calculation on Index Column
7. (02:08) Expand.
8. (02:26) Re-arrange columns.
9. (02:32) Summary of steps for ranking
10. (02:44) Load.
11. (02:51) Total row in an Excel Table (Power Query Excel Output).
12. (03:06) Update data and fresh data source.
13. (03:23) Summary, Closing and Video Links

[Anthony_Lecoq]

The double grouping was a good trick ;) and Alt + = shortcut was the icing on the cake ;)

[wayneedmondson]

Hi Mike. Nice one.. Monday fun.. ranking in Power Query using Group By. Thanks for the great tips and techniques on how to get that done. Thumbs up!!

[johnborg]

Thanks Mike. Very Nice!!!! You've done this in another video. Thanks Again. :)

[nsanch]

Thanks for the grouping trick Mike. I'm focusing on learning Power Query, so I'm studying, going through your videos, and hopefully I can get a good base understanding of it by the time your book comes out, and then I can switch over to that :) Thank you Mike.

[theFactsPage]

Thank you! this was the solution I was looking for in Power Bi!

[Excelambda]

PQ king is in the building!!!Great Video!!!😊
A single cell formula just in case
=LET(p, fSales[Product], s, fSales[Sales], tr, {"Product", "Total Sales", "Rank"},
up, UNIQUE(p), r, ROWS(up), sq, SEQUENCE(r+1, , 0),
sp, SUMIFS(s, p, up), rnk, XMATCH(sp, SORT(sp, , -1)),
ar, CHOOSE({1, 2, 3}, up, sp, rnk),
rs, SORT(ar, {3, 1}),
rsf, IFS(sq=0, tr, TRUE, INDEX(rs, sq, {1, 2, 3})), rsf)

[mattschoular]

Thanks Mike. That's a great way to deal with the tie results. Much appreciated

[paspuggie]

Amazing when one needs such a great solution like this, which I had to use today Mike. Worked a treat, thank you yet again :)

[SyedMuzammilMahasanShahi]

EXCELlent video Mike. Thanks for the share.

[GeertDelmulle]

That double Group trick is double fun right there! :-)

[paspuggie]

LOVE the ' ALT += ' tip Mike...wonderful 😇🤗

[IvanCortinas_ES]

Thanks, Mike. Very good the index trick to obtain the ranking!

[darrylmorgan]

Boom!Great Tips On How To Rank In Power Query...Thank You Mike :)

[vida]

It was a great idea to group twice to achieve the desired result

[PemberiSolusi]

awesome, this very helping... success
all

[nikakalichava]

Thanks for the video! Btw. the download link doe snot work :(

[garethwoodall]

Mike going to ask if this is possible. Can you inside power query or in power pivot when there is ties use an average so the tied values share the average?

[DougHExcel]

Hopefully MSFT will put in a Rank command set for PQ!

[TheSTEPHEN]

Mike sir again another impressive and phenomenal power query tip. Well sir I have one query which I am not able to resolve will you please help me out, I have checked your videos but didn't found as such so that I may helped me. Still if you please that will be a great kind of you, how can i share the data with you? Sir its a request, I know you are a busy person but please help :)

[Sal_A]

Nice interface workaround. How would you rank it so that it shows 1, 1, 2, 3? Somehow need to subtract 1 from last two rows....use Column By Example?