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