We will show you how to find and replace values greater or less than specified values in Power Query.
You can do this easily using either the Table.ReplaceValue function or Table.TransformColumns function and combine one of these with an if statement and the <>= operators.
Here is an example of a table with numbers between 1 and 1,000.
We want to find and replace values:
- Equal or less than 10 with a 0
- Equal or greater than 100 with a 2
- Any other number with a 1
The first method is to find and replace values using the Replace Values option in Power Query.
Select the column you want to replace values in, right click and select ‘Replace Values’.
Then enter two placeholder values such as 1 and 0, for example, so that the step is created.
Now you can edit the M code to include a conditional if statement along with the operators you need.
In this case, we write an if statement with a clause for values less than or equal to 10 and a clause for values greater than or equal to 100. As below.
= Table.ReplaceValue(
#"Changed Type",
each [Numbers],
each if [Numbers] <= 10 then 0
else if [Numbers] >= 100 then 2
else 1,
Replacer.ReplaceValue,{"Numbers"})You will have to change your code depending on the numbers you want to find and replace.
This method was covered in our other article about replacing multiple values at the same time in Power Query, because uses an if statement.
You could also create a custom column and write an if statement there if you want to keep the original column as is.
The second method is to use the Table.TransformColumns function.
Here is the M Code that we use for the example data to produce the same result as the first method.
It uses an if statement and the same operators.
= Table.TransformColumns(
#"Changed Type",
{{"Numbers",
each if _<=10 then 0
else if _>=100 then 2
else 1}})Here is what the results look like after either method has been applied.
