Adding a row rank based on a different column with Power Query

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.

1. Go to the Transform tab
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"}, 
	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"}, 
	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.

1. Click the double-arrow on the table column to expand the rows
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.

9 thoughts on “Adding a row rank based on a different column with Power Query”

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


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


  3. 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.


    1. 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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s