The double grouping was a good trick ;) and Alt + = shortcut was the icing on the cake ;)
@excelisfun3 жыл бұрын
Always great to having icing on the cake : )
@wayneedmondson10653 жыл бұрын
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!!
@excelisfun3 жыл бұрын
You are welcome, Wayne! Thanks for the thumbs up : )
@mattschoular88443 жыл бұрын
Thanks Mike. That's a great way to deal with the tie results. Much appreciated
@excelisfun3 жыл бұрын
You are much welcome, Matt!!!!
@SyedMuzammilMahasanShahi3 жыл бұрын
EXCELlent video Mike. Thanks for the share.
@excelisfun3 жыл бұрын
You are welcome for the share, SMM Teacher ; )
@SyedMuzammilMahasanShahi3 жыл бұрын
@@excelisfun :D so nice of you Mike Teacher ;)
@Excelambda3 жыл бұрын
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)
@excelisfun3 жыл бұрын
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!
@nsanch01813 жыл бұрын
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.
@excelisfun3 жыл бұрын
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
@johnborg54193 жыл бұрын
Thanks Mike. Very Nice!!!! You've done this in another video. Thanks Again. :)
@arifhidayat3093 жыл бұрын
Great.... Excel made eazy.. 😊
@excelisfun3 жыл бұрын
Glad it is great for you, arif!!!
@renz-m8o3 жыл бұрын
Awesome! More PQ tips please.
@excelisfun3 жыл бұрын
I have 100s already. Here is playlist: kzbin.info/aero/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
@theFactsPage3 жыл бұрын
Thank you! this was the solution I was looking for in Power Bi!
@vida17193 жыл бұрын
It was a great idea to group twice to achieve the desired result
@excelisfun3 жыл бұрын
It was an idea I learned from our Teammate Bemint back in 2017 : )
@Luciano_mp3 жыл бұрын
Thanks Mike!👍
@excelisfun3 жыл бұрын
You are welcome, Luciano!!!
@GeertDelmulle3 жыл бұрын
That double Group trick is double fun right there! :-)
@excelisfun3 жыл бұрын
Yes, I learned it from Teammate Bemint back in 2017 : )
@IvanCortinas_ES3 жыл бұрын
Thanks, Mike. Very good the index trick to obtain the ranking!
@excelisfun3 жыл бұрын
I learned it a few years ago from our Teammate Bemint : )
@paspuggie483 жыл бұрын
Amazing when one needs such a great solution like this, which I had to use today Mike. Worked a treat, thank you yet again :)
@excelisfun3 жыл бұрын
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 : ) : )
@PemberiSolusi3 жыл бұрын
awesome, this very helping... success all
@excelisfun3 жыл бұрын
Glad it helps!
@comarlopez3 жыл бұрын
Best teacher ever.
@excelisfun3 жыл бұрын
Thanks, M A L!!!! Glad the video helps!
@paspuggie483 жыл бұрын
LOVE the ' ALT += ' tip Mike...wonderful 😇🤗
@excelisfun3 жыл бұрын
Total Rows are as simple as Alt + = . Glad you like it, Paul!
@paspuggie483 жыл бұрын
@@excelisfun I even looked up the support.microsoft webpage and couldn't find that shortcut LOL
@excelisfun3 жыл бұрын
@@paspuggie48 There are so many things that Excel can do, so many, so many, that are not listed anywhere in Microsoft support : (
@paspuggie483 жыл бұрын
@@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 ;)
@sachinrv13 жыл бұрын
Fantastic 👍
@excelisfun3 жыл бұрын
Glad you like it!
@chrism90373 жыл бұрын
Awesome Mike, thanks!
@excelisfun3 жыл бұрын
Always glad to help our Team!!!!
@darrylmorgan3 жыл бұрын
Boom!Great Tips On How To Rank In Power Query...Thank You Mike :)
@excelisfun3 жыл бұрын
You are BOOM Welcome, darryl : )
@edge58173 жыл бұрын
Beautiful as always! Thanks Mike
@excelisfun3 жыл бұрын
Glad you like it, Edge!!!
@FRANKWHITE19963 жыл бұрын
Wow! I love your smart aproach 🙌🙌🙌🙌
@excelisfun3 жыл бұрын
Glad you love it, FRANKWHITE1996 : ) I learned it back in 2017 from our Teammate Bemint.
@nareshbisht63613 жыл бұрын
Awesome Job
@excelisfun3 жыл бұрын
Glad it is awesome for you : )
@DougHExcel3 жыл бұрын
Hopefully MSFT will put in a Rank command set for PQ!
@excelisfun3 жыл бұрын
Hopefully : )
@simfinso8583 жыл бұрын
Added to my knowledge Bank.
@excelisfun3 жыл бұрын
That is a good bank account ; )
@simfinso8583 жыл бұрын
@@excelisfun Yes.In Above video any new Record which secure 2nd position will Automatically updated ?
@excelisfun3 жыл бұрын
@@simfinso858 Yes, change the numbers and click refresh - you will see : )
@hazemali3823 жыл бұрын
Professor ♥
@excelisfun3 жыл бұрын
Glad you like it as always, Hazem : )
@hazemali3823 жыл бұрын
@@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_A3 жыл бұрын
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?
@excelisfun3 жыл бұрын
Use a PivotTable. PivotTable yields: 1, 2, 2, 3
@Sal_A3 жыл бұрын
@@excelisfun yeah I think you meant 1,1,2,3. But was curious if you knew in PQ.
@TheSTEPHEN0093 жыл бұрын
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 :)
@excelisfun3 жыл бұрын
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!!!
@alfredoiglesias78563 жыл бұрын
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?
@excelisfun3 жыл бұрын
I have no idea how to do that. Sorry. Maybe try: mrexcel.com/forum
@silverfunnel68193 жыл бұрын
First
@excelisfun3 жыл бұрын
You get the first place trophy!!!!
@simfinso8583 жыл бұрын
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?
@excelisfun3 жыл бұрын
I think I have a great solution with the new Dynamic Spilled Array formulas. I will try to make a video tomorrow for you : )
@excelisfun3 жыл бұрын
Here is a single cell formula using Microsoft 365 Excel: =SUM(PeriodPMTStart*(1+PercentIncreaseForEachPMT)^SEQUENCE(TotalPeriods,,0)*(1+PeriodRate)^SEQUENCE(TotalPeriods,,TotalPeriods-1,-1))
@excelisfun3 жыл бұрын
What version of Excel do you have?
@excelisfun3 жыл бұрын
Wow!!!! Here is the Old School Formula: =SUM(PeriodPMTStart*(1+PercentIncreaseForEachPMT)^(ROW(INDIRECT("1:"&TotalPeriods))-1)*(1+PeriodRate)^ABS(ROW(INDIRECT("1:"&TotalPeriods))-TotalPeriods))
@excelisfun3 жыл бұрын
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.