Error Reporting in Power BI ⚠️ (Don't miss watching this!)

Summary

The video demonstrates how to build an error-reporting table in Power Query. It helps identify and review data-quality problems—such as text in an integer column or an Excel #N/A—instead of leaving them hidden in a failing refresh.

Error-reporting workflow

Using a Customers query as an example, the presenter:

1. Adds an index column to identify the source row for each problem.
2. Uses Keep Errors on the table to retain rows containing at least one error.
3. Unpivots all columns except the index, creating a column name and a value for each field.
4. Applies Keep Errors to the value column to isolate the cells that contain errors.
5. Uses Power Query’s try keyword on those values, then expands the resulting record to reveal the error’s reason, message, and detail.
6. Removes the original value column and loads the report table into the Power BI model.

The report identifies the row number, column name, and error details, making it easier to investigate and correct the underlying data. It can also support reporting, such as counting errors or filtering by column or error reason. If the source contains no errors, the report table will be empty.

Guides and resources mentioned

• A separate tutorial on try and otherwise in Power Query.
• The presenter’s DAX and Power Query courses, which cover fundamentals and more advanced problem-solving.

Main speaker

Chandeep, creator of the Goodly channel.