Suppose you have two columns of data, one is something to group on and the other is something you could use to rank the rows. It might look something like this (grouping on Person, ranking based on Event Date):

Now let’s say that you’d like to use Power Query to add a column that ranks each row based on Event Date, but groups it by Person, like this:

To do this in the past, I have referred to Imke Feldman‘s response on this forum question more times than I can count. She has a wonderful little gif that shows exactly how to do it, and expands on it to add the sorting on page 2. In this post I’ll just break her method down step by step.

First, add a grouping by whichever column you want to group on. In this case, it’s Person.

After this step, your data preview should look something like the next image. If you click inside one of the cells that has the word “Table” (but don’t click on the word Table itself, just somewhere inside the cell), you’ll see what that table holds for that grouped value. Here, we see the four rows of data for Juno on the bottom of the query editor. This is just showing us what’s inside that specific table.

Here’s where the neat trick comes in. All we need to do is edit the M code that’s displayed in the formula bar in the image above to give us the rank (if you don’t see the formula bar, click on the View tab and check the box next to Formula Bar). The code that was written when we grouped the row in the previous step was:

To add the rank, we need to edit line 3 in the above code. We also need to add our new column to the very end. In this case I named the new column “Row Rank” and chose to order it ascending, but you could also order it descending if you want to rank backwards. The new code should be:

Now when you click in a cell with “Table”, you’ll see that the table includes the new “Row Rank” column.

Finally, all you need to do now is expand the AllRowsGrouped column and select all the columns except the one you grouped on, which in this case was Person.

Your beautiful new grouped row rank appears. Thank you, Imke!

Double, double toil and trouble;Fire burn and caldron bubble.