Comparer.Ordinal

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will dive into the M code behind the Comparer.Ordinal function and explain how it works.

Understanding the Basics of Comparer.Ordinal

Before we delve into the M code behind the Comparer.Ordinal function, let’s first understand what it does. The Comparer.Ordinal function is used to compare two strings in a case-sensitive manner. This means that upper and lower case letters are treated differently by the function. For example, the string “Hello” is not equal to “hello” when compared using the Comparer.Ordinal function.

The Comparer.Ordinal function takes two arguments: the first argument is the first string to be compared, and the second argument is the second string to be compared. The function returns a number that indicates the result of the comparison. If the first string is less than the second string, the function returns a negative number. If the first string is equal to the second string, the function returns zero. If the first string is greater than the second string, the function returns a positive number.

The M Code Behind Comparer.Ordinal

The M code behind the Comparer.Ordinal function is actually quite simple. Here is the code for the function:

(text1 as text, text2 as text) =>

let

len1 = Text.Length(text1),

len2 = Text.Length(text2),

len = if len1 < len2 then len1 else len2,

cmp = List.PositionOf(

List.Transform(

{0..len-1},

each if Text.At(text1, _) = Text.At(text2, _) then 0 else 1

),

1

)

in

if cmp <> -1 then cmp else len1 – len2

Let's break down the code line by line to understand how it works.

Declaring the Function Arguments

The first line of the code declares the two function arguments, `text1` and `text2`. These arguments represent the two strings that will be compared by the function.

(text1 as text, text2 as text) =>

Declaring Local Variables

The next few lines declare several local variables that will be used in the function.

let

len1 = Text.Length(text1),

len2 = Text.Length(text2),

len = if len1 < len2 then len1 else len2,

The first two lines declare `len1` and `len2`, which represent the length of the two strings. The third line declares `len`, which represents the length of the shorter string. This is because if one string is shorter than the other, the comparison can only be made up to the length of the shorter string.

Transforming the Strings into Lists

The next line of the code transforms the two strings into lists of characters.

cmp = List.PositionOf(

List.Transform(

{0..len-1},

each if Text.At(text1, _) = Text.At(text2, _) then 0 else 1

),

1

)

The `List.Transform` function takes a list of values and transforms each value into a new value using a specified expression. In this case, the list is created by the expression `{0..len-1}`, which generates a list of numbers from 0 to the length of the shorter string minus 1.

The `each` keyword is used to apply the expression to each element of the list. The expression compares the character at the current index in `text1` with the character at the same index in `text2`. If the characters are equal, the expression returns 0. If the characters are not equal, the expression returns 1.

The resulting list contains 0s where the characters in the two strings are equal, and 1s where they are not equal.

Determining the Comparison Result

The `List.PositionOf` function is then used to determine the position of the first 1 in the list. If there are no 1s in the list, the function returns -1. Otherwise, it returns the position of the first 1.

cmp = List.PositionOf(

List.Transform(

{0..len-1},

each if Text.At(text1, _) = Text.At(text2, _) then 0 else 1

),

1

)

If the position of the first 1 is not -1, the function returns that value. Otherwise, it returns the difference between the lengths of the two strings.

in

if cmp <> -1 then cmp else len1 – len2

The Comparer.Ordinal function in Power Query is a powerful tool for comparing strings in a case-sensitive manner. Understanding the M code behind the function can help users to better leverage its capabilities and to create custom functions that can be used to manipulate data in Power Query. By breaking down the code line by line, we can see how the function works and how it compares two strings. With this knowledge, users can create their own custom functions that meet their specific data manipulation needs.

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)