Quick Tips: Conditionally Replace Values Based on Other Values in Power Query

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].

Sample Data in Power BI

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”

Power Query Replace Values

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

Replace Values in Power Query

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"})
Conditionally replace values in Power Query

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.

5 thoughts on “Quick Tips: Conditionally Replace Values Based on Other Values in Power Query

  1. I believe you should substitute Replacer.ReplaceText with Replacer.ReplaceValue. Otherwise, the record [A=”1″,B=”11″,C=”5″] will be changed to [A=”1″,B=”55″,C=”5″].

    This can mess up the columns data types, so you should re-ascribe them using:
    =Value.ReplaceType(#”The Replaced Value Step”,Value.Type(#”The Step Before Replaced Value”))

    1. Hi Omri,
      Thanks for your feedback. You are right, Replacer.ReplaceValue is safer.
      My aim was to explain how to conditionally replace values in an easy way. I endeavour to keep the “Quick Tips” series as short and as informative as possible.
      But you raised a very sensible point.
      Again, thanks for contributing.
      Cheers

    1. Hi Kiok,

      Welcome to BIInsight.com.
      To reference a column you need to mention the referencing query name, along with the referencing column in brackets.
      So, the format will look like #”QUERY_NAME”[COLUMN_NAME]. The result is a list of values of that particular column.

      I’ve explained referencing a column from another query here.

      Hope that helps.
      Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.