Errors in Excel Power Query data can quickly be removed or replaced. Microsoft provides options for both, we’ll explain what causes errors, how to correct them, and cover some best practices to avoid them from happening in the first place.
While there are several options to filter, remove, and replace errors, it can be equally important to understand why they occurred. Many times they are indications of problems in the underlying data that can lead to incorrect calculations and inaccurate analysis.
Let’s jump in!
Table of Contents
What are Excel Power Query Errors?
Errors in Power Query datasets generally occur when there are different data types within a single column, or there is an issue with a formula used to create a custom column causing the calculation to fail. For example, multiplying a text value times a number. The operation is impossible and will result in a column full of errors.
Depending on the use case of the data, errors may need to be fixed or replaced to avoid disruptions in the flow of data transformation. Other times they can appropriately be removed. The exact way that you fix an error in Power Query will be determined by the incoming data and the specific use case.
The following screenshot shows a column with several Errors in the Zip Code column of a dataset.
Errors can be easily spotted when working with small datasets, but if they are further down in a larger dataset you may need to use some of Power Query’s Data Profiling tools.
How to See Errors in Power Query
To identify errors in an Excel dataset, import it into Power Query, and then navigate to the View section of the Power Query Ribbon and enable Column Profile and Column Quality settings. These options will sample the Excel dataset and give an indication of both the total number of errors and percentage of errors in a column.
The screenshot below shows the Column Quality panel enabled at the top of a dataset that shows the percentage of valid, error, and empty cells within a column.
When the Column Profile view is enabled, Power Query will display column statistics at the bottom of the dataset to show how often values occur within the data. In this example it does not show the distribution of values which would normally display a chart of how often a value occurs because it is unable to calculate the distribution due to the presence of Errors.
Note: By default, Excel will only profile the top 1000 rows which is indicated at the bottom of the Power Query Screen. Click on the setting and adjust it to sample the entire dataset to avoid having undetected errors in your data.
Using Excel’s data profiling tools within Power Query is a massive time saver when working with large datasets that would otherwise be difficult to manually review.
Now let’s look at how to fix Excel Power Query errors.
Replacing or Removing Errors in Excel Power Query
Power Query provides users with a couple of different options when working with error rows within Power Query. You can either right click on a column header containing the errors and Remove Errors, or you can Replace Errors.
When choosing to Remove Errors the entire row will be filtered from the dataset, and when replacing them you will be prompted to type in a value to replace them.
Both options can be accessed quickly by right clicking on a column header that contains errors.
We would caution people against instantly removing the errors without looking at the source data. Errors typically can occur when importing from an Excel file, or CSV and columns are mis-aligned due to changes in formatting or merged values.
It’s possible that an error is an indication of a bigger problem in a dataset.
Note: You can select multiple columns by holding down CTRL + Left Click on multiple columns or hold down SHIFT + left click to select a range of columns. After selecting multiple columns, you can fix multiple columns of errors at one time.
Replacing Errors with 0 in Excel Power Query
To replace errors with a zero, or any other value, right click on a column name and select Replace Errors. A Replace Errors popup will appear to enter in the value that you want to replace the errors with.
It’s recommended to replace errors with a value that’s a similar data type to the rest of the column to provide continuity for calculations.
Replacing Errors with Null Values in Excel Power Query
To replace errors with null values in Power Query, right click on a column name and select Replace Errors. Then use the value “null” without any quotes. While some systems require null values to be defined in a special way, Excel Power Query only requires the lower case word, null.
Null values are special in that they represent the absence of information. You can replace errors with a null regardless of whether the column is defined as a numeric data type or a text data type.
Removing Errors in Power Query
To quickly remove errors in Power Query, right click on a column name that contains errors and select Remove Errors. The entire row will be filtered out or removed from the dataset. If you do not want to remove the entire row of data, it may be more appropriate to replace the error with a value.
After errors are removed, a new applied step will be created. You can always go back to this applied step and delete it to add back the error rows later.
How to Filter Errors in Power Query
Errors do not appear in the regular filter options of Power Query. To Filter on Errors, select a column that contains errors, then navigate to the Home section of the Power Query Ribbon, click the Keep Rows button, and select Keep Errors. This effectively applies a filter to only see rows with errors.
Another option is to enable the Column Quality option under the View section of the Power Query Ribbon. This will show you the type of values in a column. It also allows you to click on the % of error values and either Keep Errors or Remove Errors.
As you can see, Power Query makes it very easy to remove, replace or filter on Errors. However, the best course of action is to try and avoid errors coming into your dataset in the first place. Here are some best practices.
How to Avoid Errors in Excel Power Query Datasets
To minimize errors in Excel Power Query values, especially when connecting Excel to a database, or consolidating multiple external Excel or CSV files, consider these best practices.
- Data Source Integrity – Regularly validate the accessibility and stability of your Excel and database sources, ensuring consistent format and structure.
- Column Data Type Consistency – Format each column in Excel with a specific, uniform data type to prevent mixed-type errors.
- Pre-import Data Validation – Prior to importing into Excel, check your data for accuracy, completeness, and consistency to avoid importing errors.
- Simplified Transformations – Keep Power Query formulas and transformations simple and efficient for easier debugging and lower error risks.
- Use Check Figures – Consider creating parallel queries or summary outputs to ensure that output data is complete when compared to total row counts or sums of incoming data.
Whenever possible, we recommend trying to fix errors at the data source level even though we recognize it is not always practical. Many errors can be caused by poor controls related to data entry, or some source systems generating reports that are not consistent which can at times be mitigated by connecting Excel to the source system instead of relying on pre-made reports.
Excel gives users a number of ways to identify, filter, replace, or remove errors. The fastest way to work with Power Query in many cases is right clicking on a column header. The most frequently used options for data transformation and preparation are there and can be applied to multiple columns at one time by holding down CTRL or SHIFT to select multiple columns at once.
Errors typically occur due to mismatches in data types or calculations that can’t be performed on the underlying data. Mitigate these issues by ensuring that data is consistently coming into Power Query and at times it can be beneficial to break complex Power Query Formulas into multiple smaller steps to identify errors caused by a single transformation step instead of having to unravel a complex formula.