Table.AddRankColumn

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		The 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).
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

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:

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:

• Table.AddColumn
• Table.AddIndexColumn
• Table.DuplicateColumn

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