Replacer.ReplaceValue

Here, the parameters are:

- source: The column or table that you want to replace the values in.

- oldValue: The value that you want to replace in the column or table.

- newValue: The value that you want to replace the old value with.

- Replacer.ReplaceValueFlags: This optional parameter allows you to set flags for the replacement operation, such as ignoring case sensitivity.

How Does the Replacer.ReplaceValue M Function Work?

The Replacer.ReplaceValue M function uses a combination of List.ReplaceMatchingItems and List.Transform functions internally to replace the values in a column or table. When you call the Replacer.ReplaceValue function, it first creates a list of all the matching items in the source column or table using the List.ReplaceMatchingItems function. It then transforms the list using the List.Transform function to replace each matching item with the new value.

Examples of Using the Replacer.ReplaceValue M Function

Let's take a look at some examples of using the Replacer.ReplaceValue M function in Power Query.

Example 1: Replacing a Single Value in a Column

Suppose we have a table named "Products" with a column named "Category". We want to replace all the values in the "Category" column that say "Electronics" with "Gadgets". Here is the M code to achieve this:

In this example, we first create a variable named "Source" that references the "Products" table. Then, we call the Replacer.ReplaceValue function on the "Source" variable, passing in the parameters "Electronics" as the oldValue and "Gadgets" as the newValue. We also set the Replacer.ReplaceValueFlags to CaseInsensitive to ignore case sensitivity while finding the matching items.

Example 2: Replacing Multiple Values in a Column

Suppose we have the same "Products" table as in the previous example, but this time we want to replace multiple values in the "Category" column. We want to replace all the values that say "Electronics", "Appliances", and "Tools" with "Gadgets". Here is the M code to achieve this:

In this example, we first create a variable named "Source" that references the "Products" table. We then create a list of two lists using the List.Zip function. The first list contains the old values that we want to replace, and the second list contains the new values that we want to replace the old values with. We then call the List.ReplaceMatchingItems function on the "Source" variable, passing in the name of the column ("Category") and the "ReplaceValues" list that we just created. We also set the Replacer.ReplaceValueFlags to CaseInsensitive to ignore case sensitivity while finding the matching items.

The Replacer.ReplaceValue M function is a powerful tool in Power Query for replacing specific values in a column or table. It uses a combination of List.ReplaceMatchingItems and List.Transform functions internally to achieve this. With the examples provided in this article, you can now use the Replacer.ReplaceValue function to replace values in your own Power Query queries.

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

Upcoming Courses

Contact Us