Comparer.OrdinalIgnoreCase

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Comparer.OrdinalIgnoreCase

Understanding the Comparer.OrdinalIgnoreCase Function in Power Query M

Before diving into the M code behind the Comparer.OrdinalIgnoreCase function, it is essential to understand what the function does. The Comparer.OrdinalIgnoreCase function is used to compare two strings in a case-insensitive manner. When sorting strings, the function sorts them based on their Unicode code points. However, it ignores any differences in casing between uppercase and lowercase characters.

Here is an example of how the Comparer.OrdinalIgnoreCase function works:

let

Source = {“Apple”, “banana”, “cherry”, “berry”},

Sorted = List.Sort(Source, Comparer.OrdinalIgnoreCase)

in

Sorted

The output of this M code would be:

{“Apple”, “banana”, “berry”, “cherry”}

As you can see, the strings are sorted in alphabetical order, but the function ignores the casing of the characters.

The M Code Behind the Comparer.OrdinalIgnoreCase Function

The M code behind the Comparer.OrdinalIgnoreCase function is relatively straightforward. Here is the M code for the function:

Comparer.OrdinalIgnoreCase =

(a, b) =>

let

ua = Text.ToList(a),

ub = Text.ToList(b),

len = List.Min({List.Count(ua), List.Count(ub)}),

ord = List.PositionOf(ua{0..len – 1}, each _ < ub{0..len - 1}),

res = if ord = null then

if List.Count(ua) < List.Count(ub) then -1

else if List.Count(ua) > List.Count(ub) then 1

else 0

else

if ua{ord} < ub{ord} then -1

else if ua{ord} > ub{ord} then 1

else if List.Count(ua) < List.Count(ub) then -1

else if List.Count(ua) > List.Count(ub) then 1

else 0

in

res

The M code above uses the Text.ToList function to convert the two strings to lists of characters. It then uses the List.Count function to determine the length of the two lists. The code then determines the length of the shortest list using the List.Min function.

After determining the length, the code uses the List.PositionOf function to find the position of the first character that is different between the two strings. If the two strings are the same, the function returns a value of 0. If one string is shorter than the other, the function returns a value of -1 for the shorter string and 1 for the longer string.

If the function finds a position where the two strings differ, it compares the characters at that position. If the character in the first string is less than the character in the second string, the function returns a value of -1. If the character in the first string is greater than the character in the second string, the function returns a value of 1. If the characters are the same, the function continues comparing the characters at the next position. If the function reaches the end of the strings, it returns a value based on the length of the strings.

The Comparer.OrdinalIgnoreCase function is an essential function in Power Query M for sorting strings in a case-insensitive manner. The M code behind the function is relatively straightforward and uses a few built-in functions to compare two strings. Understanding how this function works is crucial for anyone working with Power Query M, as it can save time and effort in data transformation and analysis.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)