Excel Magic Trick 1420: Ranking in Power Query: Sort, Grouping, Indexes and Custom Columns

Download File:
See how to connect to an SQL Database, Append an Excel Table to the SQL Imported Data, Group By to add Total Units for each Product, then Sort, Grouping, Indexes and Custom Columns to Rank Total Product Units:
1. (00:14) Introduction of whole project
2. (01:29) Connect to an SQL Database that contains over 7 million records
3. (02:47) Import Excel Table
4. (03:25) Append an Excel Table to the SQL Imported Data
5. (04:06) Group By feature to add Total Units for each Product
6. (05:01) Sort, Grouping, Indexes and Custom Columns to Rank Total Product Units
7. (11:54) Change Data and Refresh
8. (12:30) Summary
Related Videos:
Excel Magic Trick 1417: Excel Functions Ranking For Unit & Total Profit for Products within Manufacturer
Excel Magic Trick 1418: PivotTables Ranking For Unit & Total Profit for Products within Manufacturer
Excel Magic Trick 1419: RANKX DAX Function & More: Ranking Profit for Products within Manufacturer
Excel Magic Trick 1420: Ranking in Power Query: Sort, Grouping, Indexes and Custom Columns

[bennettr]

The logic that goes into this is fascinating. And, more importantly, the way you explain what you're doing and why is next-level stuff. Plus, you always sound like you're having a good time. Thanks!

[ExcelInstructor]

my work inbox is flooded by links to ur
Thank you. Best teach ever does even cover it in 1%... that's how gr8 ur teaching skills are :)

[aamdigital]

This is an amazing video that saved the day for me. Literally I wasted a whole day and then finally watched this video very very carefully. This does way more than as simple rank. It actually does a RANK() BY PARTITION like the SQL function. You have to pay attention to his second index maneuver. This is a RANK BY PARTITION. AMAZING. I'm a believer in magic. Might have to buy the shirt immediately.

[LeilaGharani]

Great! Grouping by numbers is a great idea - and then subtracting the extras - very nice! Thanks for sharing.

[sherryizzie]

Thanks for sharing, Mike! I just used this trick to build my top customers’ sale report today! Queries rock!

[shoeshines]

Very cool solution to make use of the "Add Index" feature to generate the rank! Definitely easier in an Excel Worksheet or DAX, but it is a good learning exercise to understand the nuts and bolts of Power Query.

[JidduVillarin]

Awesome work! I have nothing to use this on at the moment but this has just given me another tool to use in another sticky situation. Thanks for all the great work you do on this platform.

[olaayorinde]

Thank you mike, this video is amazing. If you remember I asked for COUNTIF function in power query,  this video just gave me the solution. You are a blessing! A million thanks!

[MiscaXL]

Love your solution! Personally, I'd group by Total Quantity before adding any indexes. If you add the index row before extracting the tables, you'll get the final rank right away. Unfortunately if you do it that way you don't get to learn nearly as much as using the slightly longer way.

[sumardjo]

very very Thank you
This is good for my learn
your knowledge is useful for me
clear and excellent to your sample file

[nattawut_chatwiriyacharoen]

Thanks a lot, Mike. This video makes me clearly understand "Group By" and "Index Column" in Power Query.
Unfortunately, there is no "Sort By Column" in Power Query (but Power Pivot has this command)

[ExcelInstructor]

What about the rank going of increments of 1 meaning that after 7th rank we got 8th instead of 10. or to get 2 instead of 3.?

[jerrydellasala]

Mike - I'm running O 365 Insider. I was able to add the "Amount to Subtract" column as demonstrated, and when I look at the table for record 6319712 see 3 rows with 0, 1, 2 in the new column. However when I go to expand the table the new Subtract column does not show up! The Subtract column even shows up when I click on Table (instead of to the right), but does not show up in the Expand dialog. Bug?

[Fiktage]

As always very good explanation and something new every time (how to add Index inside of related table)! As for me, seems like you have added extra steps, after the grouping by Total Units you can simply add new Index Column and expand Records because its strange that if you can not see Rank 8? it is logical to see Rank 8 after the Rank 7. Is not it? Anyway, an example video is COOOOLLL!!!

[Alexey]

2:40, 3:20 I am sure there is an advantage, but when should I choose "connection only"?

[frederickhines]

Hello Mike,
I'm having some trouble building a spreadsheet that will transpose data from a column into row.  But, I need the data to transpose to the next empty set of rows?  Do you have any suggestions that I can use and/or any videos.  I'm currently using MS Excel 2013 and 2016.  Thank you for your consideration and help!!
Thanks,

Lynn

[bharadhanenenu]

it's really cool.... could you please help me with excel 2013 how can we so this....

[adamnewhall]

I can't seem to login to the database. Is this my local problem or something up with the database? the error I get is:
Details: "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"