Replace multiple values in one column Power Query

Power Query allows you to replace any values with new values, you might have used power query replace values option which replace the old value with new value that you provide and add one replace step in your transformations.

But when you have to replace multiple values in same column, then you can simply modify the M Query code generated by replace values to replace multiple values at one time. No need to add replace values step for each values that you want to replace.

This article demonstrate How to replace multiple values in one column in Power Query without using replace values option again and again .

As you can see, here we have some sample table.

Now, we will replace the single value first after that will replace multiple values.

Let’s replace the value Successful to S in StatusCode column.

For this, open Power Query Editor then click on Transform tab.

After that select the column where you want to replace the value then click on Replace Values option.

Now, you will see a pop up window. Here you need to provide the old values and new values.

Once, you done with this click on Ok button.

Now, you can see Power Query replace the old values Successful with new value that is S.

Also, In formula bar you can see the M query code created by Power Query to replace the values.

= Table.ReplaceValue(#"Changed Type","Successful","S",
Replacer.ReplaceText,{"StatusCode"})

On the right side under Applied Steps pane, you can see the transformation step named Replaced Value is added.

Now, we will modify this M Query code to replace the multiple values in SatusCode column.

Let’s say you want to replace the value Failure with F and Halt with S.

We will modify the above M query code by adding if condition to replace multiple values based on criteria match.

Just replace the second and third parameters of Table.ReplaceValue function.

= Table.ReplaceValue(#"Changed Type", each[StatusCode], 
each if [StatusCode] ="Successful" then "S" 
else if [StatusCode] = "Failure" then "F" 
else if [StatusCode] = "Halt" then "H" else [StatusCode], 
Replacer.ReplaceText,{"StatusCode"})

You can see below screenshot, how the second and third parameter’s values are replaced.