Power Query: Lookup 3 Lowest Prices from 3 Criteria Lookup Table. Excel Magic Trick 1738.
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1738....
Get three lowest prices. Power Query.
Use Power Query to dynamically extract the three lowest prices and the transporter names, including ties for the third position. This Power Query solution comes from Geert, The Exceleer at RUclips.
Learn how to:
1. (00:00) Introduction
2. (00:20) Look at formulas we did last videos
3. (00:34) Geert says Power Query Easy and Elegant
4. (00:47) Import Excel Tables into Power Query Editor using Keyboard shortcuts: Right-Click Key, G
5. (01:30) Keyboard to load a Query to a Connection Only
6. (02:03) Convert Three Way Lookup Table to Proper Dataset with the UnPivot feature
7. (03:42) Create 3-Way Lookup Query
8. (04:20) Merge to perform lookup. This is a “Join” or “Merge” or “Lookup”. We to Left Outer
9. (05:18) Expand
10. (05:51) Sort Ascending
11. (06:06) Why we need a hurdle value for filtering
12. (06:19) Extract nth small number, in our case 3, using Drill Down
13. (06:49) M Cod Syntax for performing a Two-Way Lookup
14. (07:04) Why # and Quotes to refer to Table or Query name
15. (07:22) Remove Space to create Cleaner M Code
16. (07:36) Copy and Paste M Code into Previous step by Inserting a new step. This saves time typing the M Code. We paste code into Table.FirstN Function
17. (08:22) Delete the temporary step that we created
18. (08:27) Extract Hurdle without performing Drill Down. We use Field Access Operator and List.Last M Code Power Query Function
19. (09:27) Copy and Paste M Code into Previous step by Inserting a new step. This saves time typing the M Code. We paste code into Table.SelectRows Function
20. (09:58) Delete the temporary step that we created
21. (10:04) Remove Columns and Re-arrange field order
22. (10:17) Load Report with Keyboard
23. (10:40) Test Solution by changing conditions. Then adding new data.
24. (11:21) Summary, Closing and Video Links
Get three lowest prices. Power Query.
The keyboard shortcuts alone awards this a Gold medal. The Power Query content bumps it up to Platinum. Thanks Geert and Mike. Awesome..
Thanks for the gold medal, Matt! With Teammates like Geert and you, it is a platinum experience : )
Boom!Awesome Power Query Solution...Thank You Mike :)
Lucky we have a great Team with smart guys like Geert : )
Boom! It was a lot of PQ fun : )
Master in action!!! :-))) Thanks Mike :-)
Thanks, O Power Query Poet : ) : ) : ) : )
Thanks Mike for this EXCELlent video
You are welcome, Fellow Teacher Syed MM : )
Those small tweaks to the steps are absolutely amazing to know, Thanks Mike
Small tweaks are fun : )
Love it Mike, you are my hero 😇
Glad to help : ) We have a good Team!!!!
Thanks Mike. Key Board Shortcuts info was great. : ) : )
Glad you liked it, Formula Guy John : )
Thanks
You are welcome, Rusdi : ) : ) Luck we have a great Team : )
PQ king!!😉✌
Thanks, LAMBDA king ; ) P.S. What an awesome Team of kings we have: you, Geert and so many others : ) : )
Just wht I was lóoking for ..something new learnt today...thank u so much Mike for this.
You are welcome, Jay!!! We have a great Team!!!!
@@excelisfun plz say Hi to all in ur team..high respect and regards to all..
Amazing mike sir 👍🏻👍🏻👍🏻👍🏻
Glad it is amazing for you, Vijay. Thanks to Geert for the cool idea.
Fantastic Mike! Happy Memorial Day
Happy Memorial Day, Chris M !!!! : ) Go Team!!!!!
Thanks for the quick video, Mike.
I can't fathom how you are able to turn out these animated videos so fast!...
The level of optimization of your video workflow is as mindboggling as your Excel knowledge. :-)
As a bonus you even integrated the formula for the PositionalHurdle into the report query!
Good stuff! :-)
Mike, here's a suggestion:
In the PQ-M Options I've changed the default Load To behavior to Connection Only.
That saves a lot of steps because that's what we want most of the time.
You got that wrong: The level of optimization of your video workflow is as mindboggling. lol You should see that back and forth, up and down, film and delete and re-film mess that I call making a video. lol^2 There is not optimization, just continual chaos with determination to make a video that can be fun and maybe teach some cool things.
BTW, thanks to you for the great PQ solution. Next time, though, I will use your Power Query Golden Rule: reduce the data as much as possible, a soon as possible. Then instead of row then column, I can use column then row : ) : ) : ) : ) : )
@@GeertDelmulle That is a hot tip.
@@excelisfun it was either that or “you’re a machine”. Well, I guess it’s option 2 then. LOL! :-)
@@GeertDelmulle lol!!!
Great to see you are not afraid to share source file, I see too many either hide them or want payment.
That's alot of fun
I am glad that it is fun for you, Hussein!!! Lucky we have such a great Team : )
awesome
Glad you like it, sadyaz64!!!! Thanks to Geert for PQ fun!!!
Hi Mike (and Geert). Awesome solution! So much value in this video for learning new techniques and procedures. Love the way you use the UI to assemble M code components and then replace them back into the stream of steps and then delete the steps you used to get them. I see many videos that leave all the steps in place, constantly referring to a Base step to start over. This is a much better way to go. Love the keyboarding too! For anyone who does not have the Menu key, you can use the sequence: SHIFT+F10, G. Thanks for sharing this great lesson :)) Double thumbs up to you great Excel masters!!
Love it:
For anyone who does not have the Menu key, you can use the sequence: SHIFT+F10, G!!!!
"use the UI to assemble M code components"
Keyboards
Great Excel Teammate Masters: Geert, excelisfun, Wayne and many more : )
Go Team!!!!!
@@excelisfun Go Team!! :))
Thanks for another awesome trick 👻👍
You are welcome, kiwikiwo!!! Thanks for the ghostly thumbs up : )
Thank you Sir 👍
Now this is a real keyboard warrior!
Keybaords are fun : ) : )
Solving this problem with PQ definitvely (in my oppinion) is the easiest way. Only the requirement to show more than 3 prices if some are equal makes it difficult! I don't use shortcuts that much, but ctrl-+ to insert rows is my favorite...😉
That keyboard to insert rows is great, Bart!!!
I agree completely. At first I didn't account for ties.
Then I did, but uniformly (just insert a Group By and an IndexFromOne and Filter),
Then I learned that only the ties for the lowest requested rank (the highest number, e.g. 3) should be considered.
And then you need this solution. :-)
Yes, in many contact situations you gotta show that tie. PQ might be easier, BUT... Formula update instantly : )
I have been wondering how to pass some kind of parameters to PQ...now I have an Idea.
Thanks, Mike
You are welcome!
damn, love this content..
Glad you love it!!!! Love the user name ; )
Great as always, Thanks Mike, just a little tip for those working with laptop and dont have right click key, they can use shift+f10 instead.
Also for close and load we can right click on it and add to quick access toolbar then use alt+its number
LOVE that hot tip, Software Train!!!. Smart tags came out in Excel 2002. I remember teaching that keyboard back in the '00 and early '10s. But have long since forgotten that trick. Thanks for the hot tip. I gotta remember to show that one too : ) Go Team!
Thank you very much Mike for another awesome video! I'm wondering if you could kindly make a power query trouble shooting video? For example, I have encountered 2 situatuons that will lead to duplicated data. 1. There were times that an excel sheet might be duplicated at the "Get Excel Data" step. 2. When merging queries, if the relationship is not one to many, data got duplicated. Perhaps you've already mentioned these in different videos. I think if there will be a video gathering all the trouble shooting ideas, it will be super helpful to beginners like me. Just an idea. Thanks again Mike! Have a lovely weekend!
Great video Mike! Thank you for sharing this!
I really enjoy the way you combined query steps into a single step it makes the code so much cleaner.
I do have a question about combining query steps is there anyway I could email you with it to get your opinion?
You can ask here. Or you can try posting here for back and forth dailog to get Excel solutions: mrexcel.com/board
Great video! Though I feel like sql is better suited for these data requests?
Perhaps if the source data was in an SQL database.
Hi Mike, do you do freelance work?
Hey Mike! Is there any way to use countif function in pivot table?
First, no: you can't use a worksheet function in a PivotTable.
Second, What COUNTIFS and a PivotTable and the Group By feature in Power Query all do is exactly the same: calculations with conditions or criteria. So I am not sure why you would ever want to use COUNTIFS in a PivotTAble.
Rather than ask a general question like "Is there any way to use COUNTIFS function in pivot table", can you ask a more specific question? Then maybe I can provide more spefic answer.
@@excelisfun ok thanks. Let's use power query
@@jaichander87 I do not understand what you are trying to communicate : (
Hi. I have another question. If you are creating a measure in the 'Sales' fact table which multiplies 'Quantity' column in the Sales table to 'Price in the Product table and those two tables are related here is the syntax from one of your videos I watched:
Total Sales = SUMX(Sales, Sales[Quantity] * RELATED(Product[Price])).
What if 'Discount' is in the 'Discounts' table and that table is related to the 'Product' table and I wanna multiply these three columns (Sales[Quantity], Product[Price] and Discounts[Discount]). Is the syntax gonna be:
Discounted Revenue = SUMX(Sales, Sales[Quantity] * RELATED(Product[Price]) * RELATED(Discounts[Discount]))?
Discount times revenue gives you the discount amount. (1-Discount)*Revenue gives the net revenue. The RELATED to a discount table would depend on what type of lookup? Exact? or Approximate? For Approximate, maybe this video can help:
DAX Approximate Match Lookup & Exact Match Lookup Together for Discounted Product Price (EMT 1490), ruclips.net/video/JHh7BtRugL8/видео.html
and
DAX Approximate Match Lookup Part 2: Foreign Key & Relationship or LOOKUPVALUE? EMT1492, ruclips.net/video/fwVhBXc62Fs/видео.html
@@excelisfun Thanks. I appreciate your prompt reply
Love your work Mike. You are an excellent teacher/mentor/coach.
There are many ways to do this and perhaps this may not be suitable for the audience since I am going to bypass PQ's GUI and do a little M code but only with what is already there and simplify this line:
= Table.SelectRows(SortedRows, each [Price]
Thanks for the cool code, Craig! But I can not get that to work when there are fewer than three prices, however.
@@excelisfun Ahh... I see what you mean. Thanks for the correction
@@CraigHatmakerBXL Thanks for you solution too. If we always had three or more, than Bam! Your more efficient code would do the trick.
@@excelisfun That's gracious of you. As you know, if it isn't fit for purpose, efficiency doesn't matter.
@@CraigHatmakerBXL Very true.