Replace Values Replace values is a really useful way to search through a dataset and replace the values that you don't want with something else.

These values could be text, numeric, punctuation, even special characters like return or tab.

It applies to the entire column and it's really quick and easy way to ensure that the data is accurate.

It is case sensitive so for example, it will differentiate between Joe in capital letters and joe in lowercase.

When we perform data analysis and visualize our results, it's really important that the data we're using is consistent and accurate.

And we'll often find that the data that we have contains errors.

So we could have spelling mistakes typing errors, and even unwanted characters.

So to get rid of those we don't do it manually that would take a very long time instead. We use replace values and it can do it very quickly and efficiently in the entire column in one go.

So let's have a look at an example. If we look at the data here in column 4 the location code column we can see there are two ways in which Ireland has been referred to. So if ROI and we have IRE now if we use the data as it is in our analysis and create a chart for example to show the total of transaction amount for each location. We will find we have a separate column for ROI and a different one for IRE when they really should be the same thing. So it's important that we tidy the data first and we'll use replace values to ensure that we just have one column representing Ireland in our data set.

Let's do a workout and see how to do that in Power BI.

So I'm going to use get data to load an Excel workbook. I'm taking module 3 lesson 3 workout.

And I'm going to from the navigator select the replace values worksheet.

And use transform data to take me into query editor.

So let's have a look at some of these columns, sometimes it's difficult to see where the errors occur. So for example, if I look at location code I can maybe use the filter to help me see what values exist in the column, or I can go up to the view tab and turn on column profiling because that will also let me see what values exist in the column that way I can identify where these errors are.

So I'm going to use replace values to replace ROI with IRE so I select the column go to the home tab and use replace values.

So I'm just going to type in here, IRE and change it to ROI doesn't really matter which way round I do it But if I just click on OK here.

We can see it's updated the column here and we have just one version instead of two.

We can use Power BI to replace or even remove characters. So for example in the region column, I have these unwanted hash characters. So I'm going to select the column and use replace values to not replace them with anything else. Just get rid of them, so if I just ask it to find a hash and replace it with nothing. If I click on okay, it's removed those out.

One last example, I'm going to take the status column here. I have a couple of different ways in which failed has been entered, so I fail and I failed so I want to just make sure that I only have one spelling of that. I'll use replace values select the column replace values and I'm going to ask it first of all to replace fail and remember it's case sensitive. So I'm just putting it in an upper case here and replace it with failed. Now when I click an okay here I've got a problem because what's actually happened is it's find any occurrence of fail and replaced it with failed. So it's not quite what I was after so I'm going to just untick that over here and applied steps take it out and redo it because what I should have done if I just keep the column selected and go to replace values is I'm asking it to find fail and replace it with failed.

But I'm opening up advanced options here and asking it to match entire cell contents. It will only look for fail and replace that with the failed. So if I click an OK that's looking much better.

I can now close and apply and bring my dataset through into the report ready for some analysis.