Table.AddRankColumn
Ranks the rows by one or more columns and puts each row's rank in a new column.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.AddRankColumn(table as table, newColumnName as text, comparisonCriteria as any, optional options as nullable record) as table| Parameter | Type | Description |
|---|---|---|
table | table | The rows to rank. |
newColumnName | text | The name of the rank column. |
comparisonCriteria | any | A column name; a pair {name, Order.Descending}; or a list of them: what the rows are ranked by, lowest first unless you say descending. |
options | nullable record optional | [RankKind = RankKind.Competition] (the default), RankKind.Dense or RankKind.Ordinal: how ties are numbered. |
Returns
table. The rows in rank order, with the rank as the last column.
Remarks
- With
RankKind.Competition, tied rows share a rank and the next rank skips as many places (1, 2, 2, 4); withRankKind.Denseit does not skip (1, 2, 2, 3);RankKind.Ordinalgives every row its own rank. - Rank 1 is the lowest value unless the criteria say
Order.Descending.
Examples
Shops by sales, best first
let
Shops = #table({"Shop", "Sales"}, {{"Oslo", 120}, {"Bergen", 340}, {"Trondheim", 120}, {"Stavanger", 90}})
in
Table.AddRankColumn(Shops, "Rank", {"Sales", Order.Descending})| Shop | Sales | Rank |
|---|---|---|
| Bergen | 340 | 1 |
| Oslo | 120 | 2 |
| Trondheim | 120 | 2 |
| Stavanger | 90 | 4 |
Oslo and Trondheim tie for second place; the next shop is fourth.
Dense ranks
let
Shops = #table({"Shop", "Sales"}, {{"Oslo", 120}, {"Bergen", 340}, {"Trondheim", 120}, {"Stavanger", 90}})
in
Table.AddRankColumn(Shops, "Rank", {"Sales", Order.Descending}, [RankKind = RankKind.Dense])| Shop | Sales | Rank |
|---|---|---|
| Bergen | 340 | 1 |
| Oslo | 120 | 2 |
| Trondheim | 120 | 2 |
| Stavanger | 90 | 3 |