How table.replacevalue power query function works?

Table.replacevalue power query function helps us to replace the old value with the new value of our choice.

The syntax of Table.replacevalue function is

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

It is a pretty useful function during the data cleaning process for any Power BI project.

In this post, we will see different flavors of Table.replacevalue function in Power query.

Are you ready?

Let’s take the following example as you can see below

We have three columns

1. Employees
2. Salary
3. City

We can implement table.replacevalue function in two ways.

1. Select any column and right click to choose replace values option
2. Seelct any column, then click transform option on the ribbon and then click replace values option as you can see below

Let’s try to replace the value 2000 in the Salary column to 2500 as you can see below. You can use any of the above ways to apply replace values function in power query.

This method will replace all the values it finds “2000,” but what if we want to replace them based on a particular condition?

Power Query replace value if condition

Let’s add 500 as a bonus to all the employees if their salary is less than 5000.

We must use the if condition in the power query replace function to achieve this requirement.

Select the Salary column and click replace values function as described above.

= Table.ReplaceValue(#”Removed Columns”,1,2,Replacer.ReplaceValue,{“Salary”})

I have used “1” as an old value and “2” as a new value that we will change shortly.

= Table.ReplaceValue(#"Removed Columns",
    each [Salary],
    each if [Salary] < 5000 then [Salary] + 500 else [Salary] ,
    Replacer.ReplaceValue,{"Salary"})

Power Query replace value multiple condition

We can also use multiple conditions within Table.replacevalue function in power query.

We want to give a bonus to those employees with a salary less than 500 but belong to Canada.

The following expression will achieve the result

= Table.ReplaceValue(#"Removed Columns",
    each [Salary],
    each if [Salary] < 5000 and [Country] = "Canada" then [Salary] + 500 else [Salary] ,
    Replacer.ReplaceValue,{"Salary"})

In this blog, I try to explain the different variations of Table.replace function in Power Query, and I hope it will help you.

For more useful blogs, please visit Learn DAX