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

[@bennett49r]

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!

[@excelisfun]

As you probably know, when you know the hows and whys, then fun is easy! You are welcome for the video, Ben.

[@ExcelInstructor]

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

[@LeilaGharani]

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

[@excelisfun]

You are welcome! There is another way too - Tomorrow or Sunday I will post a slightly different way to do it with the MIN function, a trick from our Online Excel Teammate Bemint : )

[@LeilaGharani]

Very much looking forward to that! It's amazing how fast you can make all these super informative videos and still have such great quality! Thank you for all that you do!

[@CeliaAlvesSolveExcel]

Beautiful solution, Mike!

[@aamdigital8616]

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.

[@excelisfun]

No need to buy the shirt, just watch, and when you learn leave a comment and click that thumbs ups : ) : )

[@shoeshines2121]

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.

[@excelisfun]

Thank you for your amazing support, T P!!!!!

[@sherryizzie5309]

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

[@olaayorinde6865]

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!

[@excelisfun]

That is awesome: I am glad it helps with COUNTIF : )

[@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.

[@excelisfun]

You are welcome!

[@pmsocho]

Great as always!

[@excelisfun]

Thanks, pmsocho!!

[@eCabinetstipsandtricks]

Wow, That is just amazing. Thanks!

[@excelisfun]

You are welcome : )

[@sumardjo]

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

[@excelisfun]

You are welcome : )

[@armondnazarian4455]

Thanks Mike It was a great video!

[@excelisfun]

You are welcome, Armond!!

[@Al-Ahdal]

Excellent and informative as always.

[@excelisfun]

Glad it helps!

[@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.

[@excelisfun]

Can you post your M Code, so we can all see : )

[@MiscaXL]

I'm sorry but my way gave the wrong results in the end: Instead of skipping after multiple equal numbers it continued from the next number. I could've altered my code to add the number of rows from the previous to the index but the final code would've been even more complex than yours. So here's my M code that gives "almost the right results" :) : let Source = Table.Combine({fTransactions, NewRecords}), #"Grouped Rows" = Table.Group(Source, {"Product"}, {{"Total Quantity", each List.Sum([Quantity]), type number}}), #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Total Quantity"}, {{"Records", each _, type table}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows1",{{"Total Quantity", Order.Descending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Total Quantity"}), #"Expanded Records" = Table.ExpandTableColumn(#"Removed Columns", "Records", {"Product", "Total Quantity"}, {"Product", "Total Quantity"}) in #"Expanded Records"

[@excelisfun]

Thanks for posting : )

[@bistruca]

Thanks

[@excelisfun]

You are welcome!

[@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)

[@excelisfun]

Yes, sorting is tricky in Power Query.

[@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.?

[@jerrydellasala7643]

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?

[@LTV_GOD]

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!!!

[@JarosawBorzecki]

That's right! Here is an example of such a solution: 1drv.ms/x/s!Agoi3ixeRLjuhT-0bH-TNx22Ui7Y?e=TneXNb

[@excelemployeeleavetracker1274]

Great

[@mohamedchakroun4973]

Absolutelay Amazing Mike Thanks Youuuuuuuuuuuuuuuuuuuuuuuuuuuu

[@excelisfun]

You are welcome!

[@hosseinhosseinpoor9561]

عالی

[@automationguide3498]

Thank You !!

[@excelisfun]

You are welcome!

[@Alexey0795]

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

[@excelisfun]

1) When they are sub queries that you will use in other queries and you do not want to load them to sheet, 2) When you are loading to Data Model.

[@entertainmentgalaxy971]

Superb..:)

[@zaighamuddinfarooqui1705]

Excellent!!! :)

[@excelisfun]

EXCELlent comment!!!!

[@gustavopinto5863]

What about Rank 8 and 9?

[@frederickhines499]

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

[@excelisfun]

I do not know how to transpose data from a column to the next empty set of rows... : ( Maybe post question here: mrexcel.com/forum

[@adamnewhall6416]

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)"

[@excelisfun]

I checked the database. It is working. I am not sure what is causing the error. I googled the error and saw a few different responses. Try Googling. If you resolve, please post back and tell us what was causing the error. It will help me and maybe others : )

[@bharadhanenenu]

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

[@excelisfun]

Have you downloaded and installed Power Query (free download) for Excel 2013? Also, there are many other ways to accomplish this without Power Query. Here is one: kzbin.info/www/bejne/m2WtmIyvp72toaM