RankKind.Ordinal - Enumeration

RankKind.Ordinal (2) is an enumeration that specifies the type of ranking. It is a member of the RankKind.Type and indicates that all items are given a unique ranking number even if they compare as equal. This ranking method guarantees a ranking with consecutive numbers where a rank can appear only once.

Examples

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

Source table as dataset for RankKind.Type in Power Query

You can rank this data using the Table.AddRankColumn function. Below are examples of how to rank the data based on the revenue amount.

Ranking Data in Ascending Order

To rank the data by revenue in ascending order, use the following code:

Table.AddRankColumn( 
    Source,
    "Revenue Asc",
    { "Revenue", Order.Ascending },   // Sort column in Ascending order
    [ RankKind = RankKind.Ordinal ]
 )

Ranking Data in Descending Order

To rank the data by revenue in descending order, use this code:

Table.AddRankColumn( 
    Source,
    "Revenue Desc",
    { "Revenue", Order.Descending },  // Sort column in Descending order
    [ RankKind = RankKind.Ordinal ]
 )

These outcome of these expressions are stored in the last two columns in the following table:

Using Table.AddRankColumn with RankKind.Ordinal in Power Query

Using Multiple Columns for Ranking

You can also provide multiple columns in the comparison criteria if you want more control over the sorting. For example, consider a scenario where two customers have the same revenue amount of 200. The second sorting argument provides additional sorting instructions for these situations.

Ranking with Secondary Sorting in Ascending Order

To rank by revenue and then by CustomerID in ascending order, use:

Table.AddRankColumn( 
    Source,
    "Revenue V1",
    { { "Revenue", Order.Ascending }, { "CustomerID", Order.Ascending } },
    [ RankKind = RankKind.Ordinal ]
 )

Ranking with Secondary Sorting in Descending Order

To rank by revenue in ascending order and then by CustomerID in descending order, use:

Table.AddRankColumn( 
    Source,
    "Revenue V2",
    { { "Revenue", Order.Ascending }, { "CustomerID", Order.Descending } },
    [ RankKind = RankKind.Ordinal ]
 )

You can find the outcome of these two expressions in the below table:

Using Table.AddRankColumn with RankKind.Dense and multiple sorting columns

The RankKind.Ordinal enumeration assigns ranks to the rows based on their order. If there are ties (i.e., rows with the same value in the specified column), the tie-breaking logic will be applied based on the additional columns provided in the comparison criteria. This allows you to have a more precise control over how rankings are assigned, especially in datasets with duplicate values.

Other related enumerations are:

Applies to

Here’s a list of functions that work with RankKind.Type:

Table.AddRankColumn

Contribute » | Contributors: Rick de Groot