RankKind.Ordinal

Updated on

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 examples rank the data based on the revenue amount.

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

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

You can also provide multiple columns in the comparison criteria in case you want more control of the sorting. In the below example there are 2 customers with a revenue amount of 200. The second sorting argument helps in providing additional sorting instructions for these situations.

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

= Table.AddRankColumn( 
    Source,
    "Revenue V2",
    { { "Revenue", Order.Ascending }, { "CustomerID", Order.Descending } },
    [ RankKind = RankKind.Ordinal ]
 )
Using Table.AddRankColumn with RankKind.Dense and multiple sorting columns

Other related enumerations are:

Applies to

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

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot