Downloads
Power Query MSplitters and combinersBeginner

Comparer.OrdinalIgnoreCase

A comparer that treats text as equal when the letters match, ignoring capital and lower case.

Syntax
Comparer.OrdinalIgnoreCase

Returns: A comparison function. You pass it to a step that matches values, such as List.Distinct, List.Contains or List.Difference.

What it does

Many M functions that compare values accept a comparer. Comparer.OrdinalIgnoreCase tells them to ignore capital and lower case when they check whether two text values are the same. Use it for names, codes and free text typed by people, where "ACME", "Acme" and "acme" are the same vendor. Without it, the comparison is exact, and those three would be treated as different.

Syntax and arguments

Example

A vendor list was typed by hand in several offices. The AP team wants a unique list of vendor names to check against the master file.

let
    Typed = {"Acme Supply", "ACME SUPPLY", "acme supply", "Bolt Hardware"},
    Unique = List.Distinct(Typed, Comparer.OrdinalIgnoreCase),
    Count = List.Count(Unique)
in
    Unique

Unique is {"Acme Supply", "Bolt Hardware"}: the three spellings of Acme are one vendor, and the first form is kept. Without the comparer, all four values would stay.

Example: a second, different use

Checking whether a code exists regardless of case: List.Contains(ApprovedCodes, "v-1001", Comparer.OrdinalIgnoreCase).

Things to watch

In business terms

Matching vendor, customer and employee names across systems, and checking codes typed by hand, is much more reliable with case ignored. Record the choice in the query, because it changes which items count as duplicates.

Related functions

All Power Query M functions · View as Markdown · M basics