Replacing values can be an important part of cleaning up your data and preparing it for data analysis. Below, I’ll outline the steps to take to replace a value in Power Query. I’ll also show you how you can create a formula in Power Query to make it easy to replace multiple values at once.
Replacing a single value in Power Query
In the following data set, I have a list of orders. There are dates, order numbers, and statuses. Some of the statuses may be a bit similar so to reduce the number of them, it can make sense to replace values.
I am going to replace to the ‘Awaiting Authorization’ status to ‘Pending’.
Here are the steps needed to take to replace a value in Power Query:
1. Load your data into Power Query.
2. Right-Click on the column where you want to replace values and select Replace Values
3. Enter the value to find and what to replace it with, and then click OK.
Now, Power Query will replace the value for you:
This isn’t an ideal solution, however, because doing it this way would require you to repeat these steps over and over again. Instead, there’s another way to do this.
Replacing multiple values in Power Query at once
If you want to replace multiple values in a single step in Power Query, you can accomplish that through a formula. The Table.ReplaceValue function allows you to specify the values you want to replace. For instance, to replace just a single value, this would be the formula:
= Table.ReplaceValue(#"Changed Type","Awaiting Authorization","Pending",Replacer.ReplaceText,{"Status"})Where #”Changed Type” is the name of the preceding step. In this formula, any instance of ‘Awaiting Authorization’ is replaced with ‘Pending’.
If you want to replace multiple values, then you can use if statements to check for multiple conditions:
= Table.ReplaceValue(#"Changed Type",each [Status], each if [Status] = "Awaiting Authorization" then "Pending" else if [Status] = "Awaiting Shipment" then "Pending" else [Status], Replacer.ReplaceText,{"Status"})The same function is used. However, by using the ‘each’ keyword, it will now cycle through the values in the [Status] field. It will do the original search for ‘Awaiting Authorization’ and replace it with ‘Pending’. There is also an else if statement which allows the formula to go even further and also replace ‘Awaiting Shipment’ with ‘Pending’. Finally, if there are no matches for either of those terms, then it will just leave the value that is already in the ‘Status’ field.
You can even add more else if statements to replace more values if necessary. By doing this, you can make the process even more efficient by swapping out even more values through a single step.
If you liked this post on How to Replace Multiple Values in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.