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

[@Anthony_Lecoq]

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

[@excelisfun]

Always great to having icing on the cake : )

[@wayneedmondson1065]

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!!

[@excelisfun]

You are welcome, Wayne! Thanks for the thumbs up : )

[@mattschoular8844]

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

[@excelisfun]

You are much welcome, Matt!!!!

[@SyedMuzammilMahasanShahi]

EXCELlent video Mike. Thanks for the share.

[@excelisfun]

You are welcome for the share, SMM Teacher ; )

[@SyedMuzammilMahasanShahi]

@@excelisfun :D so nice of you Mike Teacher ;)

[@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)

[@excelisfun]

LOVE your LET!!! I tried LET too, but tried it with RANK.EQ which dos not work because of ref argument, but your XMATCH solves that : ) Lovely formula. I added it to workbook. Go Team!

[@nsanch0181]

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.

[@excelisfun]

The studying my videos is the way to go. My book will not go into as much detail about Power Query as the MSPTDA class does. However, the book will be the only book every written that covers all the tools, including Power Query, Power Pivot and DAX. The book will have a little M Code. The book will cover the most important parts of Power Query for getting data and some worksheet stuff. The book after will be all about Power Query and M Code. But no worries, this power query playlist has all you really need for now: kzbin.info/aero/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK

[@johnborg5419]

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

[@arifhidayat309]

Great.... Excel made eazy.. 😊

[@excelisfun]

Glad it is great for you, arif!!!

[@renz-m8o]

Awesome! More PQ tips please.

[@excelisfun]

I have 100s already. Here is playlist: kzbin.info/aero/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK

[@theFactsPage]

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

[@vida1719]

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

[@excelisfun]

It was an idea I learned from our Teammate Bemint back in 2017 : )

[@Luciano_mp]

Thanks Mike!👍

[@excelisfun]

You are welcome, Luciano!!!

[@GeertDelmulle]

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

[@excelisfun]

Yes, I learned it from Teammate Bemint back in 2017 : )

[@IvanCortinas_ES]

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

[@excelisfun]

I learned it a few years ago from our Teammate Bemint : )

[@paspuggie48]

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

[@excelisfun]

You are welcome yet again : ) I post videos, you search and find, then if they help, click thumbs up and comment. That is a TEAM in action : ) : )

[@PemberiSolusi]

awesome, this very helping... success all

[@excelisfun]

Glad it helps!

[@comarlopez]

Best teacher ever.

[@excelisfun]

Thanks, M A L!!!! Glad the video helps!

[@paspuggie48]

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

[@excelisfun]

Total Rows are as simple as Alt + = . Glad you like it, Paul!

[@paspuggie48]

@@excelisfun I even looked up the support.microsoft webpage and couldn't find that shortcut LOL

[@excelisfun]

@@paspuggie48 There are so many things that Excel can do, so many, so many, that are not listed anywhere in Microsoft support : (

[@paspuggie48]

@@excelisfun I just discovered a new one on me, adding Document Location to one's Quick Access Toolbar...and I always thought I knew a lot about Excel (which is 1% of what you and MrExcel know)...we live 'n learn Mike ;)

[@sachinrv1]

Fantastic 👍

[@excelisfun]

Glad you like it!

[@chrism9037]

Awesome Mike, thanks!

[@excelisfun]

Always glad to help our Team!!!!

[@darrylmorgan]

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

[@excelisfun]

You are BOOM Welcome, darryl : )

[@edge5817]

Beautiful as always! Thanks Mike

[@excelisfun]

Glad you like it, Edge!!!

[@FRANKWHITE1996]

Wow! I love your smart aproach 🙌🙌🙌🙌

[@excelisfun]

Glad you love it, FRANKWHITE1996 : ) I learned it back in 2017 from our Teammate Bemint.

[@nareshbisht6361]

Awesome Job

[@excelisfun]

Glad it is awesome for you : )

[@DougHExcel]

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

[@excelisfun]

Hopefully : )

[@simfinso858]

Added to my knowledge Bank.

[@excelisfun]

That is a good bank account ; )

[@simfinso858]

@@excelisfun Yes.In Above video any new Record which secure 2nd position will Automatically updated ?

[@excelisfun]

@@simfinso858 Yes, change the numbers and click refresh - you will see : )

[@hazemali382]

Professor ♥

[@excelisfun]

Glad you like it as always, Hazem : )

[@hazemali382]

@@excelisfun you are the only reference in excel Many Thanks Professor ♥

[@garethwoodall577]

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?

[@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?

[@excelisfun]

Use a PivotTable. PivotTable yields: 1, 2, 2, 3

[@Sal_A]

@@excelisfun yeah I think you meant 1,1,2,3. But was curious if you knew in PQ.

[@TheSTEPHEN009]

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 :)

[@excelisfun]

I am very sorry, but I am very busy. Try this great Excel question site: mrexcel.com/forum

[@nikakalichava8012]

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

[@excelisfun]

Sorry about that. I just fixed link!!!

[@alfredoiglesias7856]

Is there a way to to a lookup in a column where some values are already added and just fill the empty ones without a helper column?

[@excelisfun]

I have no idea how to do that. Sorry. Maybe try: mrexcel.com/forum

[@silverfunnel6819]

First

[@excelisfun]

You get the first place trophy!!!!

[@simfinso858]

A Question not Related to this video. I Want FV of an Amount with 10% Increasing pmt amount every time ( step up S.I.P.)For example in first month I will invest 5000,in 2nd month i will invest 5500 like wise for 10 years.can You show how to do it?

[@excelisfun]

I think I have a great solution with the new Dynamic Spilled Array formulas. I will try to make a video tomorrow for you : )

[@excelisfun]

Here is a single cell formula using Microsoft 365 Excel: =SUM(PeriodPMTStart*(1+PercentIncreaseForEachPMT)^SEQUENCE(TotalPeriods,,0)*(1+PeriodRate)^SEQUENCE(TotalPeriods,,TotalPeriods-1,-1))

[@excelisfun]

What version of Excel do you have?

[@excelisfun]

Wow!!!! Here is the Old School Formula: =SUM(PeriodPMTStart*(1+PercentIncreaseForEachPMT)^(ROW(INDIRECT("1:"&TotalPeriods))-1)*(1+PeriodRate)^ABS(ROW(INDIRECT("1:"&TotalPeriods))-TotalPeriods))

[@excelisfun]

For your example, I used: Years = 10 NumberCompoundPerYear = 1 TotalPeriods = 10 AnnualRate = 0.12 PeriodRate = 0.12 = PeriodPMTStart = 5000 PercentIncreaseForEachPMT: = 0.1 and I got FV = $128,026.44 Does that seem about right? I checked it on a smaller example and it seems to be the correct math.