Sometimes when you bring in data into the Power Query Editor, you experience data type errors. Here below is a range of temperatures in degrees Celcius for a few days in October. It has an average at the bottom. Normally you would remove that average at the bottom and carry on and let the Pivot Table handle the average and the sum and any other aggregations. However, you might be getting the data from another linked system, where you don’t have control of the data format. What can you do?

Here below is a very simple and small set of data. It is a range, not a table.

Let’s bring the data into Power Query. Click inside the data range. Click the Data tab, then From Table/Range. Normally we wouldn’t include the aggregation at the bottom. In this example, I do include it for illustration purposes. You might run into this challenge when you are importing data from another system, such as the web. The data may not be in the right format.

Inside the Editor, we want to check that the first column is a date format. After changing the date column to a Date data type, we see that we have an error, as shown below.

Next we want to remove the row or rows with errors. There is one. If we want to see a listing of all of our errors, click the column of concern (having an error) go to the Home tab, Keep Rows, Keep Errors.

We can look at and analyze the errors. When we are done we just have to delete the step Kept Errors in the Applied Steps by clicking the X icon in front of the “Kept Errors”.

In the Home tab, under the Remove Rows button, we have the Remove Errors choice. If we do this, it will remove the entire row of the cell that has the error.

Replace with Another Value

Suppose you are expecting a number in a cell, but you have the text “N/A” or “no data” or something else. You can replace the resulting Error with a 0, if that makes sense. How do you do that? In the Transform tab, Replace Values, Replace Values.

In other Power Query examples, you may want to replace an empty blank cell with null.