Power Query: Understanding Comparer.Ordinal Formula

Power Query is a powerful tool within Excel and Power BI, designed for data transformation and preparation. Among its many formulas, Comparer.Ordinal stands out as a function that allows for sophisticated text and data comparisons. Understanding this formula can unlock advanced techniques for data ranking, sorting, and analysis, enabling users to derive more meaningful insights from their datasets. This in-depth exploration will break down the Comparer.Ordinal formula, explaining its functionality and demonstrating its potential applications.

What is Comparer.Ordinal?

Comparer.Ordinal is a formula in Power Query's M language that allows for the comparison of two values.

Unlike simple sorting functions, Comparer.Ordinal provides a numerical output that signifies the relationship between the two values. This output indicates which value comes first in a natural sequence, making it highly versatile for various data manipulation tasks. The term 'ordinal' itself implies dealing with items in a specific order or sequence.

At its core, Comparer.Ordinal assesses values based on their inherent progression, be it alphabetical, numerical, or chronological. This can extend beyond sorting, giving deeper insights when comparing data values and figuring out their relationships. The formula adheres to a specific syntax: Comparer.Ordinal(x as any, y as any) as number.

• x as any: This signifies the first value to be compared. The 'as any' part is crucial because it indicates that the value can be of any data type recognized in Power Query.
• y as any: This represents the Second value for comparison. It too can be any data type.
• as number: This denotes that the output of the formula will be a numerical value, which will then indicate the order relationship between 'x' and 'y'.

Understanding the output is critical in effectively using Comparer.Ordinal:

• Negative Number: Indicates that x comes before y in the sequence.
• Zero: Means that x and y are equal.
• Positive Number: Signifies that x comes after y in the sequence.

The numerical output transforms how Power Query can manipulate and understand your data. The output makes it simple to generate custom rankings or sorts, taking analysis beyond what normal sorting can provide. Let's break this concept down further to see how this can be practically useful.

Deconstructing the Comparer.Ordinal Syntax

The beauty of Comparer.Ordinal lies not only in its functionality but also in its syntax. Understanding the syntax is key to leveraging the formula effectively. The general form of the syntax is:

Comparer.Ordinal(x as any, y as any) as number

Let’s dive into each element to understand how they function together.

• Comparer.Ordinal: This is the function name itself. It signifies that you are invoking Power Query’s built-in comparer function specifically designed for ordinal comparisons.

  

• x as any: As explained earlier, 'x' represents the first value in the comparison, and 'as any' means that this value can be of any data type. Power Query will automatically handle the data type appropriately.

• y as any: Similar to 'x', 'y' is the second value to be compared, also accepting any data type. This flexibility makes Comparer.Ordinal exceptionally versatile.

• as number: This specifies that the function returns a numerical value. The returned number encodes the result of the comparison: whether 'x' comes before, is equal to, or comes after 'y'.

The data type flexibility is a massive advantage as it allows the comparison of dates, numbers, and Texts uniformly. There's no need to specify a data type, which simplifies the usage. Here is an example markdown table illustrating how these elements relate to practical applications: