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.
Comments
16 responses to “Adding a row rank based on a different column with Power Query”
Very cool thanks foe sharing.. And congrats on the new blog!
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 🙂
Thank you, Alex! I’ve used this solution from Imke so many times. Hopefully it will be helpful to other users, too!
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!
Simply put ….WOW! Thank you so much Stephanie. You’ve got the gift.
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!
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?
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.
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
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.
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.
Yes! This was it….I totally overlooked. And thanks so much for responding after you posted this trick 3 years ago.
Thank you very much Stephanie. I’m so grateful I came across this very helpful blog, and I’m glad I bookmarked it after the first time as I’ve needed to return to this on a couple of occasions since!
As one or two others have mentioned, this is a common scenario. I’ve encountered this before in a SQL setting where Rank Over Partition By is the solution to get the correct rank based on a particular group, but in Power Query this was difficult to solve until I came across this article.
Thank you!
Wonderful. You posted it in 2020, and i found it in 2024.
Just Like others said in comment column. Everything works well until i am missing the “Row Rank” colum when expanding from the selection list. Luckily, i read your reply in comment. Solved. but why you don’t included it in the description above so your readers wouldn’t face the last issue.
anyway, i really say great thank you.
Thank you for posting this! I’m very new to Power BI/Query and was looking for a rank function for a while in Power Query and was surprised that it isn’t as straight-forward as other applications. I’m very impressed!
Thank you so much for this! I’ve been working on basically this problem for a couple of days now and couldn’t figure out how to achieve it in Power BI. These instructions and walkthough saved my sanity!