Handling Excel Formula Errors in Power Query

When working with data imported from Excel in Power BI, it is common to encounter formula errors such as #N/A, #REF!, and others. Since you cannot change the source Excel file directly, Power Query offers tools to handle these errors effectively during the data loading process.

Steps to Handle Errors in Power Query

1. Use the "Replace Errors" Feature

Power Query provides a built-in feature to replace errors with a specific value.

How to Implement:

• Select the column containing potential errors.
• Go to the "Transform" tab.
• Click on "Replace Errors".
• Specify the value to replace errors with (e.g., 0, NULL, or a descriptive message).

2. Applying the try...otherwise Expression

The try...otherwise expression allows you to test an expression and provide an alternative result in case of an error.

Example of DAX Expression:

Result = try [Column1] otherwise null

Explanation:

• try [Column1]: Attempts to access the value in Column1.
• otherwise null: If an error occurs, it returns null instead.

3. Filtering Out Error Rows

If the goal is to remove rows with errors altogether, you can apply a filter to exclude these entries.

Example of Filtering:

FilteredTable = Table.SelectRows(YourTable, each not List.IsEmpty(List.Select(Record.ToList(_), each _ is error)))

Explanation:

• Table.SelectRows: Filters rows based on a condition.
• Record.ToList(_): Converts each record to a list.
• List.Select: Selects items that are errors.
• not List.IsEmpty(...): Ensures that only rows without errors are retained.

4. Combining Multiple Solutions

If needed, these techniques can be combined to create a robust error handling mechanism in Power Query.

Example Scenario

Assuming you have a column named Sales that might contain errors, you can:

1. Replace errors with zero.
2. Create a new column with error-checking.

Implementation:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedErrors = Table.ReplaceErrorValues(Source, {{"Sales", 0}}),
    CheckedColumn = Table.AddColumn(ReplacedErrors, "SafeSales", each try [Sales] otherwise 0)
in
    CheckedColumn

Conclusion

Utilizing the above methodologies in Power Query will enable you to effectively manage and mitigate Excel formula errors when loading data into Power BI. This represents a proactive approach to error handling, ensuring that your analytics remain robust and accurate.

For further learning, consider exploring the Enterprise DNA Platform for comprehensive training on Power Query and DAX.