Table.AddRankColumn Function in Power Query

The Table.AddRankColumn function in Power Query is used to add a new column to a table that assigns a rank to each row based on specified criteria.

Syntax

Table.AddRankColumn(
table as table,
newColumnName as text,
comparisonCriteria as any,
optional options as nullable record) as table

The function has the following parameters:

table: The input table to which the rank column will be added.
newColumnName: The name of the new rank column (e.g., "Rank").
comparisonCriteria: A function that defines the value(s) used to determine ranking.
options (optional): A record specifying ranking behavior. Supported fields:

RankingKind: It specifies the type of ranking.

RankingKind.Competition: Ties share the same rank; the next rank is skipped (e.g., 1, 1, 3). It is default value for ranking.
RankingKind.Dense: Ties share the same rank; the next rank is not skipped (e.g., 1, 1, 2).
RankingKind.Ordinal: All items are given a unique ranking number even if they compare as equal (e.g., 1, 2, 3).

Example: Add a column named Ranking and give the ranking based on the Marks column values in the descending order.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = "Katrina", Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = "Mohini", Marks = 278], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = 875], 
      [CustomerID = 8, Name = "Anjali", Marks = 380]
    }
  ), 
  Return = Table.AddRankColumn(MyTable, "Ranking", {"Marks", Order.Descending})
in
  Return

The result of the above code is shown below:

Table.AddRankColumn function in Power Query

Let’s change the RankKind.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = "Katrina", Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = "Mohini", Marks = 278], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = 875], 
      [CustomerID = 8, Name = "Anjali", Marks = 380]
    }
  ), 
  Return = Table.AddRankColumn(MyTable, "Ranking", {"Marks", Order.Descending}, [RankKind = RankKind.Ordinal])
in
  Return

The result of the above code is shown below:

Previous Next