This article will show you how to find and replace values based on another column in Power Query.
Here is an example table with references in the first column and types of animals in the second column.
To demonstrate how to find and replace values based on other columns, we will use the ‘Reference’ table to replace values in the ‘Animal’ column.
Depending on the reference, we want to categorize the animal as a Dog, Cat, or keep it as Either.
We will show you a couple of different examples using the table below.
If we want to find and replace the value in the ‘Animal’ column with ‘Dog’ when the value in the ‘Reference’ column is 20 and ‘Cat’ when the reference is 30, then this is what you will need to do:
Firstly, right-click on the ‘Animal’ column and select ‘Replace Values’. Then enter two placeholder values, in this example, it could be ‘A’ for the value to find and ‘B’ for the value to replace.
After this step has been applied, you will want to edit the M code and add a conditional statement that uses the ‘Reference’ and ‘Animal’ columns.
The code below can be used for this example. For your work, you will need to change the parts in bold to fit your query, i.e. previous step name and column names. You can also add in more conditions to the statement to meet your needs.
= Table.ReplaceValue(
Source,
each [Animal],
each if [Reference] = "20" then "Dog"
else if [Reference] = "30" then "Cat"
else [Animal],
Replacer.ReplaceText,{"Animal"})This type of solution can also be viewed in more detail in our guide on replacing multiple values in Power Query.
In another example, if we want to find and replace the value in the ‘Animal’ column with ‘Dog’ when the value in the ‘Reference’ column begins with 2 and ‘Cat’ when the reference begins with 3, then this is what you will need to do:
Again, right-click on the ‘Animal’ column and select ‘Replace Values’. Then enter two placeholder values, in this example, it could be ‘A’ for the value to find and ‘B’ for the value to replace.
Then, after this step has been applied, you will want to edit the M code and add a conditional statement that uses the ‘Reference’ and ‘Animal’ columns, as well as text functions.
The code below can be used for this example. For your work, you will need to change the parts in bold to fit your query, i.e. previous step name, column names, etc.
You can also add in more conditions to the statement to meet your needs and use different text functions, such as Text.End, etc. You can find more text functions available in Power Query M code here.
= Table.ReplaceValue(
#"Changed Type",
each [Animal],
each if Text.Start(Text.From([Reference]),1) = "2" then "Dog"
else if Text.Start(Text.From([Reference]),1) = "3" then "Cat"
else [Animal],
Replacer.ReplaceText,{"Animal"})For both of these examples, you will end with the table like this:
