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"}, 
	{{"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.

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.

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

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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