Power Query (M) made a lot of data transformation activities much easier and value replacement is one of them. You can easily right click on any desired value in Power Query, either in Excel or Power BI, or other components of Power Platform in general, and simply replace that value with any desired alternative. Replacing values based on certain conditions however, may not seem that easy at first. I’ve seen a lot of Power Query (M) developers adding new columns to accomplish that. But adding a new column is not always a good idea, especially when you can do it in a simple single step in Power Query. In this post I show you a quick and easy way to that can help you handling many different value replacement scenarios.

Imagine you have a table like below and you have a requirement to replace the values column [B] with the values of column [C] if the [A] = [B].

One way is to add a new conditional column and with the following logic:

if [B] = [A] then [C] else [B]

Well, it works perfectly fine, but wait, you’re adding a new column right? Wouldn’t it be better to handle the above simple scenario without adding a new column? If your answer is yes then continue reading.

You can use the Query Editor UI to construct the value replacement in Power Query to avoid misspelling, case sensitivity issues, etc… and honestly, it is simpler to use the UI when possible than typing long M codes. Anyways… Let’s remove the new column we created previously and go through the second scenario.

Right click on a value in column B and click “Replace Values”

Replace the selected value with any desired value. In my example I replaced 5 with 1000.

All you need to do now is to modify the code with the correct logic. Let’s review the logic, we want to check for each value of column [B] in every single raw of the table and replace it with a value of column [C] only if [B] = [A].

I highlighted the “each” as it is an important keyword in Power Query.

So we only need to modify the Power Query code as below:

replace “5” with each [A]

replace “1000” with each [C]

So the final code would be:

=Table.ReplaceValue(Source, each [A], each [C],Replacer.ReplaceText,{"B"})

The above code finds value of column [A] in [B], if they’re equal then replaces the value of column [B] with the value of column [C].

To read more about how to reference in Power Query have a look at my previous post here.

Like this: Like Loading...