Comparers, Replacers, Combiners, and Splitters

Data preparation within Power Query involves several techniques aimed at manipulating and refining data. These techniques include splitting, combining, comparing, and replacing values to achieve the desired data structure and quality. Here’s a brief overview of each technique:

Technique	Purpose	Example
Comparers	Determine equality and order	Identifying duplicates, ranking, and sorting data
Replacers	Substitute values	Correcting misspellings or irregularities to ensure uniformity
Combiners	Concatenate...

Technical requirements

To get started, visit the GitHub repository and download the PBIX file that accompanies this chapter. That file is prepared for you to follow along and apply the techniques discussed here, offering a hands-on approach that enhances your understanding. By following along, you’re not just learning theoretically; you’re also gaining practical experience that solidifies your knowledge and skills.

Key concepts

Before we delve into the main subject of this chapter, it’s beneficial to briefly review some related topics. First, almost all comparers, combiners, and splitters yield a function value. These values are then used as arguments for other M functions. Therefore, understanding concepts related to functions and their invocation is essential. Second, what do comparer functions and the enumeration Order.Type have in common?

Function invocation

To effectively use functions within our code, understanding the invoke expression is key. The invoke expression consists of a set of parentheses that can optionally contain a list of arguments. It triggers the execution of the function body, which either returns a value or produces an error. For a more in-depth understanding of functions, please refer to Chapter 9, Parameters and Custom Functions.

The most straightforward way to call a function is to use its full name. After the name, you include an invoke expression...

Comparers

Comparer functions play a crucial role in data processing, providing a means to evaluate and establish relative order or determine equality. Take, for instance, the strings Hello, hello, and HELLO. You might question if they are identical. However, it’s comparer functions that can dictate the rules – such as case sensitivity – for how strings are interpreted and compared.

The role of comparer functions is to provide a method to compare values and determine their relative order or equality. Comparer functions are used in various scenarios, such as sorting or performing comparisons, for example, in conditional expressions.

In Power Query, these functions are mostly used as optional arguments to higher-order functions that require a comparison or equation operation. Some of them are listed here, but there are more functions equipped with a comparer or equationCriteria or comparisonCriteria parameter:

Text...

Comparison criteria

Now examine the books on your shelf; do you notice one that is out of place? Take it. Determining its proper location requires comparison. You will compare the book in your hands to others on the shelf by applying specific criteria, repeating that process until you find its rightful place. The same holds true for data. By setting relevant criteria, you can order it in meaningful ways, such as ordering month names from January to December, weekdays from Monday to Sunday, and so on.

Comparison criteria can be used to arrange data according to specific logic. In this section, we will explore various methods provided by the Power Query M language for defining comparison criteria to order values. We’ll use the List.Sort and Table.Sort functions as examples to show how you can create custom rules and gain exact control over the ordering of data.

Numeric value

When sorting data using Table.Sort, you can designate any numerical value to signify a desired...

Equation criteria

In the world of data, determining equality isn’t always straightforward. Equation criteria enable us to identify differences or commonalities between two values.

Equation criteria are used to determine equality and match data in tables or lists according to specific logic. In this section, we will explore various ways to specify equation criteria in the M language. We will use the List.Contains function to illustrate how these methods help to establish control over equality testing.

Default comparers

In this chapter, we have already covered the role of comparer functions at length, which is to provide a method to compare values, determining their relative order and equality. Therefore, you can simply pass a default comparer as equationCriteria. Here, the selected comparer acts like a built-in rulebook that List.Contains follows when comparing values. By ignoring case sensitivity, New York and new york are considered to be equal:

List.Contains...

Replacers

Replacer functions are used by other functions in the Power Query M language. There are two default replacers, Replacer.ReplaceText and Replacer.ReplaceValue, which are used as arguments to either List.ReplaceValue or Table.ReplaceValue to substitute a value. This enables users to replace specific substrings or values with new strings, new values, or expressions. These functions are commonly used in data transformation scenarios where it is necessary to modify and clean inconsistent data to obtain uniformity.

For instance, let’s take a look at this table (Figure 11.14) where we want to replace HR with Human Resources in the Department column:

Figure 11.14: Sample data

Here’s how to do it using the User Interface (UI):

• Select the Department column of the table.
• Navigate to the Transform tab on the ribbon and choose Replace values.
• A dialog box will appear. Input HR in the field for value to find.
• Enter Human Resources...

Combiners

Combiners merge text values from a list or columns within a table to form a single text value. Whether concatenating first and last names to create full names or joining address components into a complete address, combiners are essential in everyday scenarios where separate elements need to be brought together to create something more meaningful. The same principle applies to transformation scenarios, where merging columns before additional transformations, such as unpivoting, is crucial to ensure that related values remain together throughout the process. This provides the means to separate these combined values again, at a later stage in the transformation process.

In the Power Query M language, combiners join a list of text values into a single text. They are utilized by higher-order standard library functions such as Table.ToList and Table.CombineColumns, which use a combiner function to process each row in a table and produce a single value per row.

A common...

Splitters

On the other hand, splitters perform the task of dividing a single text value into multiple distinct components. Whether separating full names into first and last names or dissecting a complete address into individual elements, they can break a string apart into more granular pieces. This principle is also relevant in transformation scenarios where previously merged column values need to be separated. Splitters provide a means to reverse the merging process, separating values back into their original distinct components at a subsequent stage in the data transformation process.

In the Power Query M language, splitters are used to separate a string into a list of text values based on a specific delimiter or pattern. These are often utilized by higher-order standard library functions such as Table.SplitColumn, which is designed to divide a single column into multiple columns or rows. This is a common operation frequently used when handling strings that require parsing or...

Practical examples

Thus far, you’ve gained an understanding of the different comparers, replacers, combiners, and splitters available in the M language. Now, with this foundational knowledge, it’s time to solidify your learning through practical examples that demonstrate their use in real-world scenarios. These examples will enhance your understanding of how to integrate these functions into your own workflows.

Feel free to devise your own strategy for these scenarios before delving into them. If you’re uncomfortable with translating this into M code at this time, that’s perfectly fine. You can still formulate an outline and consider potential obstacles to overcome. This will enable you to compare your approach with the one suggested here. However, keep in mind that there are numerous solutions to any given problem; this book only presents one possible method.

Removing control characters and excess spaces

A common task in cleaning and transforming...

Summary

This chapter began by laying the groundwork for key concepts in the M language, which is crucial for understanding some of the intricacies discussed later on. We explored techniques such as splitting, combining, comparing, and replacing values, among others, using illustrative examples for enhanced comprehension. These techniques will prove invaluable in elevating data quality. Achieving proficiency will require dedication and regular practical application in your daily routine – an investment that is well worth the effort for its reward.

In the next chapter, you will learn about what an error is and how errors can be raised, contained, and detected, as well as the tools in your error handling toolkit.

Learn more on Discord

Join our community’s Discord space for discussions with the author and other readers:

https://discord.gg/vCSG5GBbyS