Table.AddRankColumn
TableAdds a rank column to the table based on the specified comparison criteria.
Syntax
Table.AddRankColumn(table as table, newColumnName as text, comparisonCriteria as any, optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to rank. |
newColumnName | text | Yes | The name of the new rank column to add. |
comparisonCriteria | any | Yes | A column name, list of column names with optional sort order, or comparer function. |
options | record | No | Optional record. Supports RankKind (e.g., RankKind.Dense, RankKind.Competition, RankKind.Ordinal) to control tie-breaking behavior. |
Return Value
table — The original table with a new column containing rank numbers.
Remarks
Table.AddRankColumn adds a new column to the table containing a rank number for each row based on the specified comparison criteria. By default, the rank is assigned in descending order (highest value = rank 1). To rank in ascending order, use Order.Ascending in the criteria list.
The options record accepts a RankKind field that controls how ties are handled. There are three supported values:
- RankKind.Competition (default): tied rows share the same rank, and the next rank after a tie skips to account for the tied positions (1, 1, 3, 4).
- RankKind.Dense: tied rows share the same rank, but no ranks are skipped (1, 1, 2, 3).
- RankKind.Ordinal: every row receives a unique rank regardless of ties; the tie-breaking order follows the row order in the table (1, 2, 3, 4).
To rank on multiple criteria (e.g., first by Revenue descending, then by Name ascending), pass a list of {column, Order.*} pairs as the comparison criteria. Table.AddRankColumn is more convenient than combining Table.Sort with Table.AddIndexColumn when you need to preserve the original row order alongside the rank.
Examples
Example 1: Rank customers by total order quantity (competition ranking)
let
Sales = #table(
{"CustomerName", "Product", "Quantity"},
{
{"Alice", "Widget A", 4},
{"Bob", "Gadget B", 2},
{"Charlie", "Widget C", 10},
{"Alice", "Gadget D", 1},
{"Diana", "Widget A", 6}
}
),
Totals = Table.Group(Sales, {"CustomerName"}, {{"TotalQty", each List.Sum([Quantity]), type number}}),
Ranked = Table.AddRankColumn(Totals, "Rank", {"TotalQty", Order.Descending})
in
RankedCustomerName | TotalQty | Rank | |
|---|---|---|---|
| 1 | Alice | 5 | 2 |
| 2 | Bob | 2 | 4 |
| 3 | Charlie | 10 | 1 |
| 4 | Diana | 6 | 2 |
Example 2: Dense ranking to avoid gaps in rank sequence
let
Source = #table(
{"Product", "UnitPrice"},
{{"Widget A", 25.00}, {"Gadget B", 50.00}, {"Widget C", 15.00}, {"Gadget D", 75.00}, {"Thingamajig E", 120.00}}
),
Ranked = Table.AddRankColumn(
Source,
"PriceRank",
{"UnitPrice", Order.Descending},
[RankKind = RankKind.Dense]
)
in
RankedProduct | UnitPrice | PriceRank | |
|---|---|---|---|
| 1 | Widget A | 25 | 3 |
| 2 | Gadget B | 50 | 2 |
| 3 | Widget C | 15 | 4 |
| 4 | Gadget D | 75 | 2 |
| 5 | Thingamajig E | 120 | 1 |
Example 3: Ordinal ranking to guarantee unique ranks
let
Source = #table(
{"Name", "Score"},
{{"Alice", 95}, {"Bob", 82}, {"Carol", 95}, {"Dave", 70}}
),
Ranked = Table.AddRankColumn(
Source,
"Rank",
{"Score", Order.Descending},
[RankKind = RankKind.Ordinal]
)
in
RankedName | Score | Rank | |
|---|---|---|---|
| 1 | Alice | 95 | 1 |
| 2 | Bob | 82 | 3 |
| 3 | Carol | 95 | 2 |
| 4 | Dave | 70 | 4 |