Table.AddRankColumn is a Power Query M function that appends a column to the table with the ranking of one or more other columns based on comparison criteria. The function returns a new table with the added rank column.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Table.AddRankColumn(
table as table,
newColumnName as text,
comparisonCriteria as any,
optional options as nullable record,
) as table| Argument | Attribute | Description |
|---|---|---|
| Table | This is the main table to add a ranking column to. | |
| newColumnName | The name for the ranking column as text. | |
| comparisonCriteria | Used to provide one or more columns sorting directions. You can provide a single list, or a list of lists for multiple criteria, example: { { “Revenue”, Order.Descending }, {“CustomerID”, Order.Ascending } } | |
| options | Optional | Allows you to provide a RankKind.Type in the form of a record. You can choose from RankKind.Competition, RankKind.Dense and RankKind.Ordinal. Provide the argument as record: [ RankKind = RankKind.Ordinal ] |
Description
Appends a column named newColumnName to the table with the ranking of one or more other columns described by comparisonCriteria. The RankKind option in options can be used by advanced users to pick a more-specific ranking method.
Examples
Imagine you have a dataset called Source with the following setup:
You can rank this data using the Table.AddRankColumn function. Below examples rank the data based on the revenue amount.
= Table.AddRankColumn(
Source,
"Revenue Asc",
{ "Revenue", Order.Ascending }, // Sort column in Ascending order
[ RankKind = RankKind.Competition ]
)
= Table.AddRankColumn(
Source,
"Revenue Desc",
{ "Revenue", Order.Descending }, // Sort column in Descending order
[ RankKind = RankKind.Competition ]
)
To make this ranking unique, you can provide provide multiple columns in the comparison criteria.
= Table.AddRankColumn(
Source,
"Revenue Asc",
{ { "Revenue", Order.Descending }, {"CustomerID", Order.Ascending } },
[ RankKind = RankKind.Competition ]
)
= Table.AddRankColumn(
Source,
"Revenue Desc",
{ { "Revenue", Order.Descending }, {"CustomerID", Order.Ascending } },
[ RankKind = RankKind.Competition ]
)
Alternatively you can make use of Ranking types like RankKind.Ordinal and RankKind.Dense.
Related functions
Other functions related to Table.AddRankColumn are:
