Table.AddRankColumn adds a new column that ranks each row against the others based on one or more columns you choose. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
If you want to number your rows in Power Query by sales, score, or any value, from highest to lowest or the other way around, this is the function that does it in one step.
Syntax of Table.AddRankColumn Function
Table.AddRankColumn(table as table, newColumnName as text, comparisonCriteria as any, optional options as nullable record) as table
where
table(required, table). The table you want to rank.newColumnName(required, text). The name for the new rank column that gets added.comparisonCriteria(required, any). How rows are ranked. Use{"Column",Order.Descending}to rank by one column, or a list of those like{{"Score",Order.Descending},{"Time",Order.Ascending}}to add tie-breakers.options(optional, nullable record). A record that tweaks the ranking. The main one is[RankKind=...], which picks how ties are handled. Omit it for the default behavior.
Returns: the original table with one extra column of rank numbers. The output rows are reordered to follow the ranking, so the result is sorted by rank, not by the source order.
In plain terms, you point it at a table, name the new column, and tell it which column to rank by. It hands back the table with a rank number on every row.
Example 1: Rank salespeople by sales
You have a list of salespeople and their sales, and you want to rank them from highest seller to lowest.
Here is the starting data:
| Salesperson | Sales |
|---|---|
| Maya | 940 |
| Leo | 1200 |
| Ivy | 760 |
| Sam | 1050 |
Rank by Sales in descending order:
let
Source = Excel.CurrentWorkbook(){[Name="Example1"]}[Content],
AddedRank = Table.AddRankColumn(Source,"SalesRank",{"Sales",Order.Descending})
in
AddedRank
The result adds a SalesRank column and reorders the rows by rank:
| Salesperson | Sales | SalesRank |
|---|---|---|
| Leo | 1200 | 1 |
| Sam | 1050 | 2 |
| Maya | 940 | 3 |
| Ivy | 760 | 4 |
Leo has the highest sales, so he gets rank 1. Notice the rows are no longer in the original order.
Example 2: Handle ties with RankKind.Competition
Two customers have the same revenue and you want tied rows to share a rank. RankKind.Competition does this, and it leaves a gap after the tie.
Here is the starting data:
| Customer | Revenue |
|---|---|
| Bob | 200 |
| Jim | 100 |
| Paul | 200 |
| Ringo | 50 |
Rank by Revenue descending with RankKind.Competition:
let
Source = Excel.CurrentWorkbook(){[Name="Example2"]}[Content],
AddedRank = Table.AddRankColumn(Source,"RevenueRank",{"Revenue",Order.Descending},[RankKind=RankKind.Competition])
in
AddedRank
Both 200 rows share rank 1, then the next rank jumps to 3:
| Customer | Revenue | RevenueRank |
|---|---|---|
| Bob | 200 | 1 |
| Paul | 200 | 1 |
| Jim | 100 | 3 |
| Ringo | 50 | 4 |
Rank 2 is skipped because two rows already occupy the top spot. This is the default behavior when you omit options.
Example 3: Close the gap with RankKind.Dense
Same data, but this time you want tied rows to share a rank with no gap afterward. RankKind.Dense does exactly that.
Here is the starting data:
| Customer | Revenue |
|---|---|
| Bob | 200 |
| Jim | 100 |
| Paul | 200 |
| Ringo | 50 |
Rank by Revenue descending with RankKind.Dense:
let
Source = Excel.CurrentWorkbook(){[Name="Example3"]}[Content],
AddedRank = Table.AddRankColumn(Source,"RevenueRank",{"Revenue",Order.Descending},[RankKind=RankKind.Dense])
in
AddedRank
The two 200 rows share rank 1, and the next rank is 2, not 3:
| Customer | Revenue | RevenueRank |
|---|---|---|
| Bob | 200 | 1 |
| Paul | 200 | 1 |
| Jim | 100 | 2 |
| Ringo | 50 | 3 |
No rank is skipped, so the numbers stay continuous even with a tie.
Example 4: Force unique ranks with RankKind.Ordinal
Same data again, but now you want every row to get a distinct rank even when values tie. RankKind.Ordinal breaks ties using the source order.
Here is the starting data:
| Customer | Revenue |
|---|---|
| Bob | 200 |
| Jim | 100 |
| Paul | 200 |
| Ringo | 50 |
Rank by Revenue descending with RankKind.Ordinal:
let
Source = Excel.CurrentWorkbook(){[Name="Example4"]}[Content],
AddedRank = Table.AddRankColumn(Source,"RevenueRank",{"Revenue",Order.Descending},[RankKind=RankKind.Ordinal])
in
AddedRank
The tied 200 rows get 1 and 2 instead of sharing a rank:
| Customer | Revenue | RevenueRank |
|---|---|---|
| Bob | 200 | 1 |
| Paul | 200 | 2 |
| Jim | 100 | 3 |
| Ringo | 50 | 4 |
Bob comes before Paul in the source, so he takes rank 1. No two rows share a rank.
Example 5: Break ties with a second column
When the main column ties, you can add your own tie-breaker instead of relying on source order. Pass a list of criteria, and the second one settles the tie.
Here is the starting data:
| Player | Score | Time |
|---|---|---|
| Amy | 90 | 50 |
| Ben | 90 | 45 |
| Cleo | 85 | 40 |
| Dan | 95 | 60 |
Rank by Score descending, then by Time ascending for ties:
let
Source = Excel.CurrentWorkbook(){[Name="Example5"]}[Content],
AddedRank = Table.AddRankColumn(Source,"Rank",{{"Score",Order.Descending},{"Time",Order.Ascending}},[RankKind=RankKind.Competition])
in
AddedRank
Amy and Ben both score 90, so the faster time wins:
| Player | Score | Time | Rank |
|---|---|---|---|
| Dan | 95 | 60 | 1 |
| Ben | 90 | 45 | 2 |
| Amy | 90 | 50 | 3 |
| Cleo | 85 | 40 | 4 |
Ben beats Amy on the tie because his Time of 45 is lower. With both columns deciding order, no tie is left for RankKind to handle.
Things to keep in mind with Table.AddRankColumn
- The rank column is added as the last column. The new column lands at the end of the table. If you want it up front, follow the step with
Table.ReorderColumns. - Ranking within groups is not built in. To rank rows inside each category, group with
Table.Groupand callTable.AddRankColumnon each group’s sub-table inside the aggregation. A single top-level call ranks the whole table as one set. - It is a newer function, so older builds may not have it. If you hit
Expression.Error: The name Table.AddRankColumn wasn't recognized, your Power Query build is too old. Update Excel or Power BI, or rank withTable.AddIndexColumnon a sorted table instead.
Common questions about Table.AddRankColumn
How is this different from sorting and adding an index column?
An index column just numbers rows 1,2,3 no matter what, so ties get different numbers and you handle ordering yourself. Table.AddRankColumn understands ties through RankKind and ranks by your criteria in one step.
Can I rank rows within groups?
Not directly. Group the table with Table.Group, then apply Table.AddRankColumn to each group’s sub-table inside the aggregation, so each group is ranked on its own.
List of All Power Query Functions
Related Power Query Functions / Articles:
