Comparer.Ordinal - Comparer Function

Comparer.Ordinal is a function in the Power Query M language that returns a comparer function using ordinal rules for comparisons. The function returns a comparer function that performs exact ordinal comparisons.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Comparer.Ordinal(
   x as any,
   y as any,
) as number

Description

The Comparer.Ordinal function returns a comparer function that makes use of Ordinal rules to compare the provided values x and y.

Ordinal Rules refer to the method of comparing values based on the Unicode values of their individual characters. In simpler terms, when strings are compared using ordinal rules, the comparison is executed character-by-character based on the numerical value (code points) assigned to each character in the Unicode standard. This type of comparison is case-sensitive and doesn’t take cultural or linguistic factors into account. It’s a direct, byte-by-byte comparison, making it fast and straightforward.

When you compare two values using this function, the outcomes are threefold:

Equal: A result of 0 means both values are identical.
Unequal: -1 or 1 means the values are different from each other
- 1 implies that, in terms of unicode character order, the first value comes after the second.
- On the contrary, –1 means the first value comes before the second.

Examples

Let’s look at a few examples on how to use Comparer.Ordinal.

When you compare the values ‘a’ and ‘a’ they are considered identical and return 0.

Comparer.Ordinal( "a", "a" ) // Output 0

Now let’s say you compare a lowercase versus an uppercase letter. Using ordinal rules these are considered different. Notice how the order of the comparison impacts the output.

Comparer.Ordinal( "A", "a" ) // Output -1
Comparer.Ordinal( "a", "A" ) // Output 1

In the first expression, the first value (‘A’) comes before the second value (‘a’) when looking at their unicode character order. The second expression shows the opposite.

In case you want to return a value that’s easier to interpret, you can combine the above comparer function with Comparer.Equals.

Comparer.Ordinal( "Cat", "CAT" ) // Output 1

Comparer.Equals(
   Comparer.Ordinal, 
  "Cat", 
  "CAT" 
) // Output false

Using Ordinal rules, you can also compare “encyclopædia” and “encyclopaedia”. Under ordinal rules these are different, whereas they are considered equivalent using Comparer.FromCulture("en-US").

Comparer.Equals( 
  Comparer.Ordinal, 
  "encyclopædia", 
  "encyclopaedia" 
) // Output false

Comparer.Equals( 
  Comparer.FromCulture("en-US"), 
  "encyclopædia", 
  "encyclopaedia" 
) // Output true

Used by

While you can use the Comparer.Ordinal function by itself, it also works together with:

Other functions related to Comparer.Ordinal are:

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

Syntax

Description

Examples

Used by

Related functions