There are times when you need to clean your dataset. Suppose you had a simple table of employees that was presented to you in a report-style layout. There are gaps in the data. We want to analyze this data and provide reports such as the average salary by the department. We need to fix the dataset. We will use Power Query to do this.

Above is a screenshot of our Excel file called FillReplaceValues34.xlxs. We’ll bring this into Power Query. Go to Data, From Table/Range and bring that into the Power Query Editor. Below is a screenshot of that.

Notice that in some blank cells we have null and in other we don’t seem to have anything.

Replace Values Before Fill Down

Fill Down and Fill Up will fill in values where there are null values. What if there is a space character? It will not fill in because it does not see a space character as an empty cell. We can fix that by using the Replace Values. If we replace the space characters with null before using the Fill Down or Fill Up we will get the desired result.

Now we can produce a nice Pivot Table and Pivot Chart.