Comparer.Equals - Comparer Function

Comparer.Equals is a function in the Power Query M language that checks the equality of two given values using a provided comparer. The function returns a logical value based on the equality comparison.

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

Syntax

Comparer.Equals(
   comparer as function,
   x as any,
   y as any,
) as logical

Argument	Attribute	Description
Comparer		Comparer Functions control how to compare values. Comparer.Ordinal tests values for equality in a case-sensitive way. To ignore capitalization when you can provide Comparer.OrdinalIgnoreCase, whereas Comparer.FromCulture can be used for a culture-aware comparison.
X		The first value in the comparison.
Y		The value to compare with.

Description

Comparer.Equals function offers a way to determine if two given values are identical or not. Based on the check it return true or false. The function supports the use a custom comparer to guide this comparison. Why is this so crucial? Well, comparisons aren’t always as straightforward as they seem. Sometimes, case-sensitivity matters, while at other times, cultural nuances are important.

So, what exactly is a comparer? Think of it as a set of rules or guidelines that determine how two values should be compared. The Power Query M language provides some built-in comparers.

The following built-in comparers are available in the formula language:

Comparer.Ordinal: Used to perform an exact ordinal comparison where every character must match exactly.
Comparer.OrdinalIgnoreCase: Used to perform an ordinal comparison in a case-insensitive manner.
Comparer.FromCulture: respects cultural variance in text instructed with a culture code.

Examples

Let’s look at a few examples to see the Comparer.Equals function in action.

Ordinal comparison

Naturally, by using the comparer Comparer.Ordinal, testing whether ‘a’ is equal to ‘a’ results in true.

Comparer.Equals( 
  Comparer.Ordinal, 
  "a", 
  "a" ) // Output: true

Now let’s say you want to see if two letters are exactly the same, with their case being a distinguishing factor. Using Comparer.Ordinal to compare the lowercase “a” and the uppercase “A” would confirm they aren’t the same.

Comparer.Equals( 
  Comparer.Ordinal, 
  "a", 
  "A" ) // Output: false

However, there are many other ways in which you can compare values, one of them being case-insensitive. The comparer argument allows you to specify the rules for the comparison.

Case-insensitive comparison

So what can you do when the case doesn’t matter to you? Say, you just want to know if they’re the same character, regardless of whether they’re uppercase or lowercase. You would use the comparer Comparer.OrdinalIgnoreCase.

Comparer.Equals( 
  Comparer.OrdinalIgnoreCase, 
  "a", 
  "A" ) // Output: true

Culture aware comparison

Another fun comparer to use with Comparer.Equals is Comparer.FromCulture. It performs a comparison in line with the rules of a given culture or locale and optionally lets you ignore case. Let’s look at a few examples.

Between languages, special characters can be considered differently. For instance, the character ‘æ’in English is equal to ‘ea’.

Comparer.Equals( 
  Comparer.FromCulture( "en-US" ), 
  "Færdig", 
  "Faerdig" ) // Output: 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 ‘ea’.

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

In conclusion, the Comparer.Equals function offers a nuanced approach to equality checks, accommodating ordinal, capital-sensitive and culturally sensitive comparisons. As you dive deeper into Power Query’s M language, understanding these comparers will undoubtedly prove useful.

Used by

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

Other functions related to Comparer.Equals are:

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