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
Comparer.Ordinal( x as any, y as any, ) as number
The Comparer.Ordinal function returns a comparer function that makes use of Ordinal rules to compare the provided values
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.
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.Equals( Comparer.Ordinal, "encyclopædia", "encyclopaedia" ) // Output false Comparer.Equals( Comparer.FromCulture("en-US"), "encyclopædia", "encyclopaedia" ) // Output true
While the Comparer.Ordinal function can be used on its own, it is also used by the following functions:
Other functions related to Comparer.Ordinal are: