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
Comparer.OrdinalIgnoreCase( x as any, y as any, ) as number
Returns a case-insensitive comparer function which uses Ordinal rules to compare the provided values
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.
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
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.
While the Comparer.OrdinalIgnoreCase function can be used on its own, it is also used by the following functions:
Other functions related to Comparer.OrdinalIgnoreCase are: