Table.AddRankColumn

Updated on

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
ArgumentAttributeDescription
TableThis is the main table to add a ranking column to.
newColumnNameThe name for the ranking column as text.
comparisonCriteriaThe Order.Type dictates the order in which elements are arranged. When omitting the argument, the function uses Order.Ascending for sorting in ascending order (from smallest to largest or A to Z). You can use Order.Descending for sorting in descending order (from largest to smallest or Z to A).
optionsOptionalAllows 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

Table.AddRankColumn function adds a new column to a table, showing each row’s rank based on selected criteria. You name this column (newColumnName), choose the table (table), and set the ranking basis (comparisonCriteria). An optional options parameter, allows you to specify ranking methods with a RankKind.Type, helpful for addressing ties. Essentially, this function organizes rows by your chosen criteria, presenting their ranks for easier comparative analysis.

Examples

Imagine you have a dataset called Source with the following setup:

Source table as dataset for RankKind.Type

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 ]
 )
Using Table.AddRankColumn with Ascending and Descending Order

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 ]
 )
Using Table.AddRankColumn with Multiple Sort by Columns

Alternatively you can make use of Ranking types like RankKind.Ordinal and RankKind.Dense.

Other functions related to Table.AddRankColumn are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-addrankcolumn