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.
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”))
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
what if each [C] is a column in another query’s table? how do you reference that column?
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.
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.
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:
Hopefully that helps,
Cheers
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
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
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
Thank you for your explanation. I was wondering if you could help me with an issue here. I’m trying to modify several columns based on another column’s value but I am having trouble with the code. I want to change the values on colums B and C to 0 if the value in column A is also 0. Do you know how to code that conditional replace?
How can I replace according values of a parameter?
If PARAMETER = “US-English” then replace “Mr ” with “Mr. ” else replace “Mr.” with “Mr”
Hi Ralph.
Welcome to BIInsight.com.
I don’t believe replacing “Mr.” with “Mr” and vice versa is a good idea. What if your text contains “Mr.” and you replace the (Mr) with (Mr.)?
The result would be “Mr..”, right? You get an extra dot at the end which I don’t think is what you are after.
Use the following expression in Power Query Editor to create a query parameter with a list of values, “US-English” and “UK-English”:
"UK-English" meta [IsParameterQuery=true, List={"US-English", "UK-English"}, DefaultValue="US-English", Type="Text", IsParameterQueryRequired=true]Now use the following expression to create a sample table with a single column named “Column” and some “Mr” and “Mr.” values. Then depending on the selected value for the query parameter we add a dot or remove a dot to the text:
letSource = #table({"Column"}, {{"Mr"}, {"Mr."}, {"Ms"}, {"Ms."}}),
#"Conditionally Replace Values" = Table.ReplaceValue(Source,each [Column], each if Parameter = "US-English" then Text.Combine({Text.Remove([Column], "."), "."}) else Text.Remove([Column], "."), Replacer.ReplaceText,{"Column"})
in
#"Conditionally Replace Values"
Hopefully that helps.
Cheers