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.
2. Click “Group By”
3. Choose “All Rows” for the Operation
4. Give it a more informative New column name, like AllRowsGrouped
5. Click OK
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:
= Table.Group(#"Changed Type", {"Person"},
{{"AllRowsGrouped",
each _,
type table [Person=nullable text,
Other Info=nullable text,
Event Date=nullable date]}
}
)
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:
= Table.Group(#"Changed Type", {"Person"},
{{"AllRowsGrouped",
each Table.AddIndexColumn(
Table.Sort(_,{{"Event Date",
Order.Ascending}}), "Row Rank",1,1),
type table [Person=nullable text,
Other Info=nullable text,
Event Date=nullable date,
Row Rank=nullable number]}
}
)
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.
2. Deselect the column (or columns) you originally grouped on
3. Uncheck “Use original column name as prefix”
4. Click OK
Your beautiful new grouped row rank appears. Thank you, Imke!
Double, double toil and trouble;
Fire burn and caldron bubble.
data witches 
Very cool thanks foe sharing.. And congrats on the new blog!
LikeLike
I was like wow! These screen shots and numbers are great and then I was like WOW this Power Query solution is even better! Thank you so much for sharing this neat technique and encouraging some users to get their hands on the keyboard with some M 🙂
LikeLiked by 1 person
Thank you, Alex! I’ve used this solution from Imke so many times. Hopefully it will be helpful to other users, too!
LikeLike
I had previously found and used other Power Query solutions to rank each row based on values in two or more columns, however, your solution is the most elegant.
Thank you!
LikeLike
Simply put ….WOW! Thank you so much Stephanie. You’ve got the gift.
LikeLike
Extremely helpful and very well explained.
The syntax of M is incredibly hard to penetrate and the ‘reference material’ from MS are skimpy to the point of insulting.
You (and I guess Imke whom you name-check) have provided a practical solution to I guess a reasonably common use-case.
My suspicion is many times people want to get a grouped-index column, really MS should look to provide that natively without having to go through such hoops. (But at least now I know the hoops and how to jump through them!)
Thanks from Ireland!
LikeLike
Thank you for this. Everything works well until I expand the column. When I expand the columns, the “Row Rank” column is missing from the selection list. Any ideas?
LikeLike
Hi Jeff – make sure to add the “Row Rank=nullable number” as the last line in the Table.Group bit. It’s easy to overlook and may be what’s making the column not appear when you try to expand.
LikeLike
Pretty cool, thank you so much!
PQ is working on a Rank Cloumn, but obviously it isn’t rolled yet
https://learn.microsoft.com/en-us/power-query/rank-column
LikeLike
This helped me a ton, but when I get down to the row rank as an option to expand our, it’s not even there in my ALLROWS drop-down fields. All the others are there. I’ve been scratching my head on ranking this hierarchy data for a few days and your solution is the closest I’ve gotten and I’m 99.9 percent there, but still confused.
LikeLike
hi colin – make sure to add the “Row Rank=nullable number” as the last line in the Table.Group bit. It’s easy to overlook and may be what’s making the column not appear when you try to expand.
LikeLike
Yes! This was it….I totally overlooked. And thanks so much for responding after you posted this trick 3 years ago.
LikeLike