Handling Excel Formula Errors in Power Query

Earlier this month I published a new and very practical Power BI app on Microsoft AppSource. The app audits Excel workbooks and detects changes and errors. To implement the app I needed to resolve key obstacles that are very common when you import Excel workbooks using Power Query. How do you import the data from Excel if you have formula errors in unpredictable cells?

In today’s tutorial, you will learn how to avoid these errors when you import the data from Excel to Power Query. As a bonus, you will also learn to detect the erroneous cells and find out the type of errors and their coordinates, so if needed, you can report back to the owners of the workbooks and ask them to fix these errors.

The Challenge

Let’s examine a very simple table in Excel as shown in the screenshot below (You can download it here). You can see that we have errors in the Price and Amount columns. Formula errors in Excel are very common. Sometimes, these errors are kept by design, sometimes, they are there by mistake.

When you import data from Excel using Power Query, you may find in the preview pane the errors. But oftentimes, when you deal with a very large dataset, you may completely miss them.

Import the Excel (here), convert the type of the Price column to Fixed decimal number (or let the auto-detection of column types work its black-magic), and apply any filter on Price. For example, in the screenshot below, I applied a greater than 100 filter.

I am sure you have seen these errors before. In the next sections, we will discuss possible solutions.

Removing Rows with Errors

One of the common approaches to resolve the errors is to filter out rows with errors. To do it, you can right-click on the headers of the columns with the errors (In our case, Price and Amount), and select Remove Errors in the shortcut menu.

This step will remove rows that have errors in the selected columns. This approach may be too harsh. Oftentimes, you may know the root cause for the errors and apply some logic that to workaround the errors instead of ignoring the entire rows.

Still, let’s assume that you have no options, and need to remove the errors. We can make your challenge more interesting, and say that you need to remove rows if you find errors in ANY column. How would you approach it?

Looking at the M formula after you removed the errors for columns Price and Amount, you can see that the column names were explicitly called out in the formula, using a list.

= Table.RemoveRowsWithErrors(#"Changed Type", {"Price", "Amount"})

To remove rows with errors for any columns, you can replace the static list with the function Table.ColumnNames. Here is the modified formula:

= Table.RemoveRowsWithErrors(#"Changed Type", Table.ColumnNames(#"Changed Type"))

Note that in the formula above, in my example, I had #”Changed Type” as the last step in Applied Steps, so I used it as the parameter for Table.ColumnNames. When you apply this technique on your own data, you will have a different step name and will need to copy it as the relevant parameter.

Replacing Errors with other Values

A more common approach to handle errors in Power Query and avoid the refresh errors, is to replace the errors with values. For example, you will oftentimes want to replace the errors with nulls.

Replacing the errors for a single column is very discoverable. You right-click on the column’s header and select Replace Errors in the

But in our example, we have two columns with errors. See, what happens when you select both Price and Amount. Right-clicking on one of their headers, will not have an option for Replace Errors. But when something is missing in one UI element, it doesn’t mean that this functionality is not supported. Let’s look for it in the ribbon.

You can see in the formula bar the following line:

= Table.ReplaceErrorValues(#"Changed Type", {{"Price", null}, {"Amount", null}})

We now have a different format in M to replace the errors. You can see a list of pairs. Each pair includes the column name and the null value.

What would you do to replace errors with nulls when you don’t know in which columns you have the errors? Here is the solution using Table.ColumnNames and List.Transform.

= Table.ReplaceErrorValues(#"Changed Type", List.Transform(Table.ColumnNames(#"Changed Type"), each {_,null}))

The formula above gets the list of column names and transform them into the required pairs of column name and null. If you are not sure how it works, don’t worry. You can apply it to any table you have in the future, by clicking the fx button in the formula bar, and then copy the formula above. Don’t forget to replace the two occurrences of #”Changed Type” with the last step name of your table. If you have spaces or special characters in the last step, use the #”…” wrapper.

Detecting Errors

So far we have learned to remove formula errors or replace the errors with values. Now, let’s move to a more advanced scenario. Imagine that after you resolved the errors, you still want to dynamically detect these errors and report them to the Excel workbook’s owner.

In this section, you will learn how to do it.

Note: if this use-case has a very high value for you, you can install my Excel Compare app from AppSource here, and use it to report the errors in Excel.

If you are an advanced Power Query user and wish to detect Excel formula errors in Power Query across many workbooks, you can create the following custom function in Power Query:

(tbl) =>
let
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(tbl, {"ID"}, "Column", "Value"),
    #"Kept Errors" = Table.SelectRowsWithErrors(#"Unpivoted Columns", {"Value"}),
    #"Added Custom" = Table.AddColumn(#"Kept Errors", "Custom", each try [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Error"}, {"Error"}),
    #"Expanded Error" = Table.ExpandRecordColumn(#"Expanded Custom", "Error", {"Reason", "Message", "Detail"}, {"Reason", "Message", "Detail"})
in
    #"Expanded Error"

Name the custom function FnDetectErrorsInWorksheet.

Before you use the function, make sure you have a column ID in the table. If you don’t have one, you can create it using the Add Index button in Add Column tab. To apply the function on your table, you can invoke the FnDetectErrorsInWorksheet on your table. For example, if the table with the errors is represented in the Table query, this formula will do the trick.

= FnDetectErrorsInWorksheet(Table)

How to detect errors step by step

If you want to learn how to detect the errors without the custom function above, let’s show you the steps in the UI. You will find it very easy.

On the Queries pane, select the query with the errors. Right-click on it and select Reference in the shortcut menu. Name the new query Detect Errors.

Right-click on the header of ID column, and select Unpivot Other Columns. Now, all the data in the Excel table is rebuilt as the value and its coordinates. ID represents the row. The Attribute column represents the column name, and the Value column represents the value in the original Excel table. You can see that the Value column contains the errors.

Right-click on the header of the Value column.

In Home tab, select Keep Rows, and then select Keep Errors.

You are now left with the errors only. You already know at this stage where errors are located.

But can you tell what kind of errors were detected? To do it, we will add a custom column. In Add Column tab, click Custom Column.

In the Custom Column dialog box, enter the formula:

try [Value]

This formula returns a record with the details of the error. You can learn more about the try statement here and check out the latest official error handling article here.

It’s time to move on. Click OK and close the Custom Column dialog box.

Click the expand control in the Custom column header.

Select HasError and Error fields and click OK.

Click the expand control in the Error column header. Select the fields Reason, Message, and Detail, and then click OK.

You can now see that we extracted the specific Excel formula errors in the Message column. From here, you only need to load the Detect Errors query and build a nice report to notify your colleagues when they have errors in Excel.

Did you find this tutorial helpful? Do you want to learn more?

Schedule a very special Power BI or Power Query training for your team. Learn more here.