Comparer.FromCulture - Comparer Function

Comparer.FromCulture is a function in the Power Query M language that returns a comparer function based on the specified culture and case sensitivity. The function returns a comparer function for culture-aware comparisons.

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

Syntax

Comparer.FromCulture(
   culture as text,
   optional ignoreCase as nullable logical,
) as function

Description

Comparer.FromCulture returns a comparer function based on a specified culture, allowing users to control the comparison’s case sensitivity. You can specify a true or false value in the ignorecase argument to specify whether to ignore the case. If the ignoreCase parameter is skipped, the function by default assumes a case-sensitive, or ordinal comparison.

So what do we mean by culture here? Essentially, we’re referencing the standardized text representations of locales from the .NET framework. These culture codes perform a comparison in line with the rules of a given culture or locale. For an exhaustive list of culture codes, check the provided link.

When you pit two values against each other using this function, the outcomes are threefold:

A result of 0 means both values are identical.
1 implies that, in terms of Unicode character order, the first value precedes the second.
On the contrary, –1 means the first value comes after the second.

Examples

Comparer.FromCulture is all about context. It compares values based on a locale’s specific nuances and also offers an option to ignore the case. Let’s dive into some illustrative scenarios.

Case sensitivity

You can use the “en-US” locale to test the letters “z” and “Z” for equality. The function is case-sensitive by default.

Comparer.FromCulture( "en-US" )( "z", "Z" ) // Output: -1 (false)
Comparer.FromCulture( "en-US" )( "Z", "z" ) // Output: 1 (false)

Both outcomes, -1 and 1, stress that the characters aren’t the same. The difference? -1 indicates that the first value (“Z”) comes before the second (“z”), while 1 suggests the reverse.

But what if you’d rather overlook the case? Instruct the function to ignore the case by providing the boolean value true in the second argument.

Comparer.FromCulture( "en-US", true )( "z", "Z" ) // Output: 0 (true)

If instead, you want to return true or false, you can combine the above function with Comparer.Equals.

Comparer.Equals(
  Comparer.FromCulture( "en-US", true ),
  "a", 
  "A" 
) // Output: true

Culture aware comparisons

Languages bring their own spin to characters. In English, ‘æ’ is synonymous with ‘ae’.

Comparer.FromCulture( "en-US" )( "Færdig", "Faerdig" ) // Output: 0 (true)

However, languages like Danish beg to differ. For them, the same character holds its own unique value. In Danish, the word for ‘Finished’ is ‘Faerdig’, and it doesn’t equate ‘æ’ with ‘ae’.

Comparer.FromCulture( "da-DK" )( "Færdig", "Faerdig" ) // Output: -1 (false)
Comparer.FromCulture( "da-DK" )( "Faerdig", "Færdig" ) // Output: 1 (false)

But culture-awareness doesn’t mean overlooking case-sensitivity. Here’s how they play together. To ignore the case you can write:

Comparer.FromCulture( "da-DK", true )( "Færdig", "FÆrdig" ) // Output: 0 (true)

Whereas the following statement respects the case:

Comparer.FromCulture( "da-DK", false)("Færdig", "FÆrdig" ) // Output: -1 (false)

In wrapping up, the Comparer.FromCulture function offers a blend of cultural respect and case-sensitivity control. It’s a tool to make comparisons precise and respectful of linguistic differences.

Used by

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

Other functions related to Comparer.FromCulture are:

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