Excel Magic Trick 1429: Power Query Ranking with Index, Grouping and MIN Function!!!

Download Files:

See how to rank values in Power Query using an Index, the Group By feature and MIN Function.

Database Credentials:
Database: boomerang
User: excelisfun
PW: ExcelIsFun!

[LeilaGharani]

That's smart! Min on Index. Thanks to Bemint and thanks to you for sharing this alternate method. Always great to see the different methods to get to the same place - no matter long or short.

[williamarthur]

One thing I didn't think right was was that 7 to 10, rather than joint 7 to 8,
if you group by eg. 'Product', Sum, Units, sort High to low, Group by Total Units 'All Rows', Add Index, expand and just retain 'product',
you then get for example; joint 1, 2, 3 4 rather than 1, 1, 3, 4 . Thanks once again for all your help and inspiration.

[Bhavik_Khatri]

Wow, this is bloodly awesome. Thank you very much for sharing this tip.

[JonathanExcels]

Sometimes you present a summarized list with the top 10 (or 5) and then 'all other'. You can achieve this with grouping within the pivot table but that only works with a one-and-done analysis. If some new data point is added subsequently then it destroy's your group.

you can also have a strict top 10 pivot and then a second pivot with only a grand total. Subtraction will give you the 'all other' group. This can also go awry if there are ranking ties. Then you might have >10 rows representing the top 10 which makes charting difficult.

Using the method described in this video you can get a list of top 10 Products. Then merge the list of top 10 products back with the original data. If the merge results in null, then call that the 'all other' products, else use the Product name. Then you have one pivot that does what you want and it is dynamic.

Thanks for this tip.

[olaayorinde]

Hi, please direct me to your video that show a cell with text separated by delimiter. you separated the text in cell into different rows. Thanks in adv

[philthomas]

in the olden days it was possible to pass parameters to a web query via cells in Excel to create a dynamic web query. Is it possible to do the same in Excel 2016 via get&transform?

[brianxyz]

That was some great outside the box thinking by Bemint. By the way, what happened to videos 1427 and 1428?

[Muuip]

To bypass GroupBy minimun in order to return the minimum of each category I used to sort AZ with Excel native and delete all duplicates of the categories. Worked great with Excel native but not with Power Query, regardless if PQ is sorted AZ or ZA, it uses the sorting of the source Excel table. Which means it sometime returned the Minimum, sometime the Maximum and other times random values if source table was not sorted. Now with GroupBy, how can the value of column "C" corresponding to the Minimum of each category in column "B" be returned in a new column? Thanks.

[williamarthur]

Taken one step further; Inspired by MSPTDA 08,
one you get to add index, Add custom column 'All Rows'[column name ] to create list of products, Remove All Rows, and Extract your list of Product names, so your tied products appear on the same line.