nice one, am still learning mcode :) ! i can also suggest to use filter the dates by "is latest", its easier than manipulating m code and will give also latest transaction
The Force is strong with you! Always happy to meet an expert who stresses the importance of mastering the hotkeys in Excel. 😀 As for the solution, I would have done three small things differently: 1) It makes sense to first filter by the set by the selected Client and then aggregate using the Table.Max. Aggregation is an expensive operation, you want to run it on the reduced set. 2) Coding Range("I2") in your change macro is not a stable implementation imao. IRL Users tend to ruin the worksheet integrity by adding rows, columns, merging cells (the worst sin ever!) and such. I would create a named variable for the dropdown cell and use it in the code instead. 3) ThisWorkbook.RefreshAll may be a very expensive command, depending on how many queries do you store in your file. You could go safer by targeting only the query you need with ThisWorkbook.Worksheets("Robust").ListObjects("Orders").QueryTable.Refresh But don't get me wrong please: your guide is brilliant and Power Query is a gem worth talking about. Makes me sad to see so many Excel users missing its value. Thank you!
Wow! Thanks Nabil for this wonderful tutorial! Not only the main topic of last record retrieval, but I learnt many more things about Power Query, which I didn't knew. Now I can use all these for many more purposes! 🙂
Great explanation! Thanks Nabil for sharing this wonderful Tutorial in Excel... Following your tutorial recently, getting the ideas those were not known to me. Thanks a lot for educating the new Ideas which could be used in need...
Dear Nabil, I used dynamic array to solve the problem easily: =TAKE(SORT(FILTER(Orders,Orders[Full Name]=Client[Select Client]),6),-1) 🤗 I forgot to say that I liked your solution. I thought it was great. 🤗
What a great tutorial. I tackled many of the dynamic arrays and other functions. Now I’m learning Power Query. This tutorial gives me an idea for a work situation: last design/ project number I did for a client. Is there way to reference a named cell instead of the cell address for Target.Address?
Dear Nabil, Another way, with dynamic array functions, to solve the problem: =FILTER(Orders,(Orders[Full Name]=Client)*(Orders[Date]=MAXIFS(Orders[Date],Orders[Full Name],Client)))🤗
Thank you for watching my tutorial, and my goal was to explain this little known Table.Max function. But I agree with you that there are many other solutions (that's the versatility of Excel) and here is one for you with Dynamic arrays: =XLOOKUP(MAXIFS(Orders[Date],Orders[Full Name],Client),Orders[Date],Orders)
Dear Nabil, Using the MAXIFS function, you also get the same result: =INDEX(Orders,XMATCH(MAXIFS(Orders[Date];Orders[Full Name],Client);Orders[Date]),SEQUENCE(,COLUMNS(Orders[#Headers]))) 🤗
Maxifs is nice but why all these functions? You can simplify it by just putting the Maxifs in a Filter function: =FILTER(Orders,Orders[Date]=MAXIFS(Orders[Date],Orders[Full Name],Client[Select Client]))
nice one, am still learning mcode :) ! i can also suggest to use filter the dates by "is latest", its easier than manipulating m code and will give also latest transaction
Excellent work, it helped me a lot. I filtered nearest record below 26th of each month from 13 years data set using your concept (Table.Max).
The Force is strong with you! Always happy to meet an expert who stresses the importance of mastering the hotkeys in Excel. 😀
As for the solution, I would have done three small things differently:
1) It makes sense to first filter by the set by the selected Client and then aggregate using the Table.Max. Aggregation is an expensive operation, you want to run it on the reduced set.
2) Coding Range("I2") in your change macro is not a stable implementation imao. IRL Users tend to ruin the worksheet integrity by adding rows, columns, merging cells (the worst sin ever!) and such. I would create a named variable for the dropdown cell and use it in the code instead.
3) ThisWorkbook.RefreshAll may be a very expensive command, depending on how many queries do you store in your file. You could go safer by targeting only the query you need with ThisWorkbook.Worksheets("Robust").ListObjects("Orders").QueryTable.Refresh
But don't get me wrong please: your guide is brilliant and Power Query is a gem worth talking about. Makes me sad to see so many Excel users missing its value. Thank you!
Thanks for sharing your knowledge.
Another superb solution. Power Query is wonderful!!! Thank you Nabil!!!
Power Query is amazing! Thank you Nabil!
You are welcome
Wow! Thanks Nabil for this wonderful tutorial! Not only the main topic of last record retrieval, but I learnt many more things about Power Query, which I didn't knew. Now I can use all these for many more purposes! 🙂
@Vijay Your nice comments always motivate me to keep doing better. Thank you!
Great explanation! Thanks Nabil for sharing this wonderful Tutorial in Excel... Following your tutorial recently, getting the ideas those were not known to me. Thanks a lot for educating the new Ideas which could be used in need...
Loved it brother. Just the right thing i was looking for
Again you nailed it Nabil
Thank you Naved for watching, and glad you liked it.
Dear Nabil,
I used dynamic array to solve the problem easily:
=TAKE(SORT(FILTER(Orders,Orders[Full Name]=Client[Select Client]),6),-1) 🤗
I forgot to say that I liked your solution.
I thought it was great. 🤗
Thanks for sharing... but the goalis to explain Table.Max function
Thanks a lot, Nabil! Your PQ-solution is the Max! :-)
Thanks for watching Geert
What a great tutorial. I tackled many of the dynamic arrays and other functions. Now I’m learning Power Query. This tutorial gives me an idea for a work situation: last design/ project number I did for a client.
Is there way to reference a named cell instead of the cell address for Target.Address?
Magic as usual 🤠🤠
Dear Nabil,
Another way, with dynamic array functions, to solve the problem:
=FILTER(Orders,(Orders[Full Name]=Client)*(Orders[Date]=MAXIFS(Orders[Date],Orders[Full Name],Client)))🤗
Amazing!!!
Thanks!!
😀😀Great, Thanks alot
You're welcome 😊
Please give presentation on right from beginning to advance on power query.
nice tips but why not use advance filter?
Thank you for watching my tutorial, and my goal was to explain this little known Table.Max function. But I agree with you that there are many other solutions (that's the versatility of Excel) and here is one for you with Dynamic arrays:
=XLOOKUP(MAXIFS(Orders[Date],Orders[Full Name],Client),Orders[Date],Orders)
Dear Nabil,
Using the MAXIFS function, you also get the same result:
=INDEX(Orders,XMATCH(MAXIFS(Orders[Date];Orders[Full Name],Client);Orders[Date]),SEQUENCE(,COLUMNS(Orders[#Headers]))) 🤗
Maxifs is nice but why all these functions? You can simplify it by just putting the Maxifs in a Filter function:
=FILTER(Orders,Orders[Date]=MAXIFS(Orders[Date],Orders[Full Name],Client[Select Client]))
@@Officeinstructor Or in an XLOOKUP function: 😀
=XLOOKUP(MAXIFS(Orders[Date],Orders[Full Name],Client),Orders[Date],Orders) 🤗
@@Officeinstructor Your formula is not working for me as it lists the searched name and all others where the maximum date matches. 🤔
@@JoseAntonioMorato I wrote it without having Excel open and I will need to add the client name as a second condition for the filter. Thanks
@@JoseAntonioMorato Your XLOOKUP function is Not working for all clients, test by selecting "Garrison Danforth"
lol