Power Query - Conditionally Replace Values in a Column with Values from another Column

Colin Maitland, 27 May 2022

In this article, I will demonstrate how to use Power Query to conditionally replace values in a column with values from another column. We want to do this with the least number of steps, while handling blank values and maintaining the data type of the column for which values are to be replaced.

For example, you might use Power Query to load a list of Contacts for which you have a Country column for the Contact and a Country column from the Contact’s Parent Organisation. However, in some instances, the Country for the Contact is blank and so should be replaced with the Country from the Parent Organisation if any.

The following table shows some Contacts being loaded into a Power BI data model from a source system such as Dynamics 365. You will notice that only one of these Contacts has a Country. The others are blank.

The desired result after loading this data into Power BI is shown in the following image. This includes preserving the text data type of the Country column.

This demonstration includes two initial approaches using Replace Values and Add Custom Column, a recommended approach using Replacer.ReplaceValue and for comparison a not recommended approach using Replacer.ReplaceText.

Replace Values

Using the standard Power Query Table.ReplaceValue() function you can simply replace blank Country values with a single specific Country such as Atlantia.

This approach requires either one Replace Values step to check for and replace null values and a second Replace Values step to check for and replace empty text values, or alternately, the #"Replaced Value" step to be manually edited as shown here. In this example, the second parameter has been manually edited from null to each if [Country] = null then null else "".

This approach is suitable when the Country for all Contacts where the Country is null or empty should be replaced with a single specific Country. It is, also a suitable approach to use if you want to transform existing non-blank Country names such as AUS to Australia, NZ to New Zealand and USA to United States of America etc.

Add Custom Column

Using the standard Power Query Table.AddColumn() function you can add a custom Conditional Column to the query populated with either the Country from the Contact or the Country from the Parent Organisation depending on whether the Country from the Contact is blank or not.

However, this adds a new column rather than replacing values in the Country column. Additional steps are required, to remove the original Country column and then rename the new column to Country.

In addition, you need to either manually edit the Added Conditional Column step or add a subsequent Table.TransformColumnTypes() step, to ensure that the data type of the new custom column is set to Text. In this example, the #"Added Conditional Column" step has been manually edited by adding a “, Text.Type” clause as the last parameter of the Table.AddColumn() function.

Conditional Replace Values using Replacer.ReplaceValue

Having considered two initial approaches, the manually configured step demonstrated here provides the desired result with fewer steps.

In this example …

· [Country] in the third line is a reference to the old value.

· null and "" in the fourth line values that need to be replaced.

· [#"Country (Parent Organisation)"] in the fifth line is a reference to the column from the same row that is to be used to replace blank Country values.

· [Country] in the sixth line is a reference to the Country column and is used to replace non-blank Country values with itself.

· Replacer.ReplaceValue in the seventh line is the function to be used for replacing values.

· {"Country"} in the eighth line is the name of the column that is to be evaluated by the Table.ReplaceValue() function.

However, this approach changes the data type of the Country column from Text to Any. This can be resolved by either adding a subsequent Table.TransformColumnTypes() step, or by manually editing the Replace Value step to include an outer Table.TransformColumnTypes() step. In this example, I have edited the #"Replace Value" step to include an outer Table.TransformColumnTypes() step.

Conditional Replace Values using Replacer.ReplaceText

Using Replacer.ReplaceText instead of Replacer.ReplaceValue does not work if any of the rows in the Country or "Country (Parent Organisation)" columns contain blank values.

Here you can see that the null and empty values in the Country column have not been replaced.

This can be resolved by writing something much more complex such as the following …

The two innermost Table.ReplaceValue() clauses convert null and empty Country values to X using Replacer.ReplaceValue. The next Table.ReplaceValue() clause replaces X Country values from "Country (Parent Organisation)" using Replacer.ReplaceText. However, because, "Country (Parent Organisation)" is null for the last row the Country value for that row remains set to X, therefore, an additional outermost Table.ReplaceValue() step is used to convert any remaining Country values of X back to null using Replacer.ReplaceValue. The data type of the Country column is preserved as Text rather than being changed to Any.

However, I don’t recommend this approach. It is too complex, and most likely doesn’t perform well, because of the multiple nested Table.ReplaceValue() logic. It is also complex because of the use of Replacer.ReplaceValue for the two innermost and the outermost Table.ReplaceValue() steps and then the use of Replacer.ReplaceText for the in-between Table.ReplaceValue() step.

Conclusion

In conclusion, knowing how to conditionally replace values in one column with values from another column in a single step is a useful skill. When doing so you need to consider how to handle null and empty blank values and how to ensure that the data type of the column for which values are being replaced is not changed from its original data type such as Text to Any. My recommended approach, and the least complex, is that demonstrated in the Conditional Replace Values using Replacer.ReplaceValues section of this article.