Power BI – Replace Values in Power Query

By: David Rohlfs

Introduction

There are a lot of reasons why your incoming data will be wrong. It could be because of the system that inputs the data, the person who is adding data to the server, the server itself, or the way that you imported the data. But wrong data is never good! So, how do we fix this?

One way is by using the replace values function in Power Query. The replace values function has two parts; the replace values, and the replace errors.

In this blog we will be going over when to use the replace values and replace error’s function, and a short walkthrough demonstrating the two.

 

When to use it

Using a replace values function is straight forward. The value that you specify will be replaced by a value that you specify. But because of this there are several things that you should know about when you use a replace values function.

 

Ways to Get to a Replace Values

This isn’t normally a section that I do because we will go over how to use replace values in the walkthrough. But the replace values function is so popular that Microsoft has put the option in a couple of locations.

The first is in the Transform Ribbon.

The second is in the Column Header.

And the third is by right clicking on a cell in the query preview. This option will pre-enter the value that you clicked on.

 

Different Uses

Some of the more common uses I have with the replace values function are:

• Creating only blanks or nulls
• Getting rid of a single value
• Replacing errors
• Fixing typos
• Cleaning your Data

 

Replace Errors

The only difference between the Replace Values and Replace Errors is that the replace errors has a pre-defined value to search for. Because of this you only need to enter the value that you want to replace an error with in the pop-out.

 

Advanced Options

There are two options in the advanced options, the match entire cell contents and replace using special characters options.

The match entire cell contents option makes it so that your entry value must match the entirety of the cell to be replaced. I like this option a lot because it allows for greater ability in replacing bad fields.

The replace special characters option is not as useful though. Using this option will make it so that you are replacing a value with a tab, indent, non-breaking-space, and a couple more options. But the only time that you are likely to use these is if you are working in a niche situation.

 

Spaces in Values

When you are using the replace values function, the values that you specify are literal. That means that if you have a stray space in one of the values, Power Query will keep that space in the searching or replacing of that value. For this reason, you should always double check your values with the arrow keys on your keyboard. Not doing this is a great way to be confused for an hour before realizing your mistake.

 

Walkthrough

For this walkthrough we will be using the replace values and replace errors function in Power Query.

To start, you need to click on Transform data and get to the Power Query.

Now we can select the query that we need to work in, click on the Transform Ribbon, and click on the Replace Values dropdown.

This will give you the option to replace all your errors with a value, or to define a value that you want to be replaced. I am going to choose the replace values option.

Now you should be inside of a pop-out. In this pop-out you can create your own value to be replaced (I always suggest copying and pasting this from your data preview), and the value that

Now we can click ok in the bottom right corner and apply this step. Once this is finished, I suggest using a group by transformation to check that you did not miss any values or put a typo in any of your options. A blog about Group By’s will be linked below.

 

Conclusion

Thankfully it isn’t hard to replace a value in Power Query. The hard part does come when you need to verify that your data is correct though. To help with this I linked a few blogs below that will show you other methods of checking or transforming your data.

 

Links to Related Articles:

Utilizing a Group By

Extract Function

Adding a Custom Column in Power Query

Group By

Column From Examples

Conditional Column

Comments