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...