One-step conditional value replacement in a column in Power Query

When you’re working a lot with Power Query, you will sooner or later have to replace values in a column, based on some conditions.

Unfortunately, there is no step for that in the Power Query editor. Usually you have to add a conditional column and then remove the old column and rename the newly created one and move it to the place of the original column if you want to keep the order. This works perfectly, but if you work a lot with Power Query, you really want to get rid of the additional steps and do the transformation in one step. In this article, I will explain to you, how you can do a conditional value replacement in one step in a column in Power Query.

Use a regular replacement as a placeholder

In our case, I want to transform the product name to a category column and replace every product that contains “bike” with the category “Bike”. All the products that don’t contain “Bike” should be declared as “Accessory”. This scenario you might remember from the article a few weeks ago where I wanted to check if a column contains a specific text in DAX.

To make my life easier, I create a normal “Replace Values” step in Power Query and just enter two dummy values:

This dummy step would replace the static text “FindValue” with the static text “ReplaceValue”:

This dialog will use the function Table.ReplaceValue. In the next step, we will make this dynamic.

Replace the static search text with a dynamic one

First, we will replace the static text we’re searching for with the dynamic value of the current row. For this, we have to add an “each” and the column name. The following recording shows how all the values are immediately changed to the “ReplaceValue”:

Here is the formula of the last step:

= Table.ReplaceValue(#"Changed Type", each [Product Name],"ReplaceValue",Replacer.ReplaceText,{"Product Name"})

By the way, in case you don’t see the formula bar, you can show the formula bar under “View” and then activate the “Formula Bar”:

Replace the static replace text with the conditional formula

At the moment, we replaced the static “SearchValue” with the dynamic value of each row. Now we have to do the same for the “ReplaceValue” of the formula.

Here you can use every conditional comparison that you want. Either you know how to write M-queries in Power Query or you can use the graphical interface for conditional columns and then just copy the code. Here I used the graphical interface to create a new conditional column:

And then I used this formula for the “ReplaceValue” in our Replace-Approach. In my case I just typed the formula, but I could also have copied it from the last example. You can use a new column for every parameter, what makes it a little easier to understand the code:

Afterwards, the value was replaces by the category as we defined in our code.

The complete code of the last step is as follows:

= Table.ReplaceValue(#"Changed Type", 
each [Product Name],
each if Text.Contains( [Product Name], "bike") then "Bike" else "Accessory",
Replacer.ReplaceText,{"Product Name"})

Summary

You’ve seen how easy it can be to dynamically replace values in a column in one single step. The more often you do that, the easier it gets. And you only have one additional step in Power Query instead of three steps as we were used to.

I personally use that approach quite often and I hope it also makes your Power Query life a little bit easier and cleaner. If you have any comments or thoughts about that, just let me know in the comments.

Download Example File

You can download the File I used for this article on the following link: