Download Excel File: https://excelisfun.net/files/EMT1738.xlsx
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 YouTube.
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.