Comparer.OrdinalIgnoreCase - Comparer Function

Comparer.OrdinalIgnoreCase is a function in the Power Query M language that returns a case-insensitive comparer function using ordinal rules. The function returns a comparer function that performs case-insensitive ordinal comparisons.

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

Syntax

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

Description

Returns a case-insensitive comparer function which uses Ordinal rules to compare the provided values x and y.

The Comparer.OrdinalIgnoreCase function provides a way to test two given values to find out if they’re equal. This particular function ignores case and applies ordinal rules for comparison. But what are these Ordinal rules?

Ordinal rules provide a mechanism to compare values based on the Unicode values of each character in the strings. In everyday language, when two strings undergo comparison using these rules, the process checks each character’s numerical value (also known as code points) as per the Unicode standard.

However, there’s an essential point to highlight: the Comparer.OrdinalIgnoreCase function is case-insensitive. It does not get influenced by upper or lower case distinctions or cultural and linguistic nuances.

When you use the function to compare two values, you can expect one of three outcomes:

Equal: A result of 0 means both values are identical.
Unequal: -1 or 1 means the values are different from each other
- A result of 1 indicates the first value precedes the second based on the Unicode character order.
- Conversely, a -1 suggests the first value is after the second in the Unicode order.

Examples

To better grasp the use of Comparer.OrdinalIgnoreCase, let’s dive into some hands-on examples:

Basic equality test

When two values are identical in terms of their Unicode character order, the function’s output is 0. For example:

Comparer.OrdinalIgnoreCase( "Duck", "Duck" ) // Output: 0 (true)

This result means that, in terms of Unicode characters, “Duck” and “Duck” hold the same position.

Handling case variations

Often, you might encounter situations where you’re unsure about the case of the values. With Comparer.OrdinalIgnoreCase, differences in case won’t hinder your comparison.

Comparer.OrdinalIgnoreCase( "Duck", "duck" ) // Output: 0 (true)

Here, even though one word has an uppercase ‘D’ and the other a lowercase ‘d’, the function considers them equal.

However, if you use Comparer.Ordinal for comparison, the result differs. For instance:

Comparer.Ordinal( "Duck", "duck" ) // Output: -1 (false)

This outcome implies that “Duck” (with an uppercase ‘D’) comes before “duck” (with a lowercase ‘d’) in the Unicode order. Reversing the order of comparison inverts the result:

Comparer.Ordinal( "duck", "Duck" ) // Output: 1 (false)

The -1 in the outcome above suggests that the first value (Duck) comes before the second value (duck) when comparing its position in the unicode character order. When you turn the values around, the outcome will also switch signs.

Return true or false

For scenarios where a simple True or False outcome is desired, you can combine Comparer.OrdinalIgnoreCase with Comparer.Equals. For example:

Comparer.Equals(
  Comparer.OrdinalIgnoreCase,
  "Duck",
  "duck" 
) // Output: true

Comparer.Equals(
  Comparer.Ordinal,
  "Duck",
  "duck" 
) // Output: false

The Comparer.OrdinalIgnoreCase function offers a powerful way to compare values in Power Query’s M language. It’s especially handy when case sensitivity isn’t a priority. You will find that many functions support its use.

Used by

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

Other functions related to Comparer.OrdinalIgnoreCase are:

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