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.

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

      1. Hi, Soheil,
        I was wondering if you could help me to find why this M query is not working:

        Table.ReplaceValue(#”Changed Type”,””,(each [ACCT_CODE] = List.Contains(#”EMP”[EMP_NUM], [ACCT_CODE])),Replacer.ReplaceValue,{“ACCT_CODE”})

        I am trying to replace null value with the account code that I can find from EMP query. With EMP_NUM as the key to link to EMP.

        1. Hi Cynthia,

          Welcome to BIInsight.com.
          Look, your code is not right, you cannot say “each SOMETHING = List.Contains(***)“.
          Please note that the output of List.Contains() is Boolean (True/False).
          Perhaps you may find the following code helpful. I’m trying to replace null values in “ProductSubcategoryKey” column from “Products” table (current table) with the values from “ProductKey” column from a different table named “Distinct ProdcutKeys” when #”Distinct ProdcutKeys”[ProductKey] = #”Products”[ProductKey]:

          Table.ReplaceValue(#”Changed Type”,null, each if List.Contains(#”Distinct ProdcutKeys”[ProductKey], [ProductKey]) then [ProductKey] else 0,Replacer.ReplaceValue,{“ProductSubcategoryKey”})

          What we are asking the query here is:

          • Look at [ProductSubcategoryKey] column from the current query
          • Replace null values with the result of the following:
            • Look at [ProductKey] column from #”Distinct ProdcutKeys” table, for each value, when #”Distinct ProdcutKeys”[ProductKey] matches [ProductKey] from the current table (#”Products”) return “True”
            • If the result is “True” then return the corresponding [ProductKey]
            • Else return 0

          Hopefully that helps,

          Cheers

          1. Hey
            Soheil, Thanks for much. Please bear with me as I am a beginner on the M query.

            I made the change as following and got “blanks”:
            Table.ReplaceValue(#”Changed Type”,null, each if List.Contains(#”EMP”[EMP_NUM], [EMP_NUM]) then [ACCT_CODE] else “0000xxxx”,Replacer.ReplaceValue,{“ACCT_CODE”})

            The EMP query and the current query has the relationship with the key [EMP_NUM].

            I can find the EMP_NUM in the EMP query. ACCT_CODE does exist non-blanks.

            Can you please let me know what I did wrong again? Thanks

          2. Hi Cynthia,

            What is your data source? Is it SQL server, MySQL or any other supported DBMS? Where is the relationship you’re referring to? Are you referring to the relationships in you source DB or in Power BI?
            I’m asking as in Power BI you make the relationships in your model while your question is related to Power Query.
            You don’t create/use relationships in Power Query.
            To make it more clear, Power BI includes Power Query layer (which is your data preparation layer) and a Model which keeps all relationships, measures, calculated columns and calculated tables.
            In data preparation layer you use Power Query (M) expression language while in the Model you use DAX expression language.

            Your question is about Power Query, before you create the relationships.

            Please elaborate,
            Cheers

  2. My data source is from the Excel sheets. You are right. I am trying to load the data and to clean up the data at the same time. The relationships are set in Power BI. Therefore, it won’t work. Thanks

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.