Managing Data Load Errors in Power BI

[aviral]

Power BI is an excellent tool for data extraction, transformation, loading, and presentation. However, there are areas that could be improved to enhance the user experience. One such area is the late detection of errors during the data loading process.

The data loading process in Power BI is modal, meaning it blocks all other activities while in progress. It provides some sense of progress, such as the number of records or megabytes of data loaded for each query, depending on the data source.

One crucial feature of the “Load” dialog is that it displays the number of records that have encountered errors for each query. Once the load process completes successfully, the analyst can view these errors by clicking on a hyperlink. This feature generally works well, but it can fail to generate the queries needed to filter errored records when dealing with large datasets.
Even when Power BI can filter the errored records in the Power Query interface, it can be challenging to quickly identify the column containing the errors. If multiple columns are involved, it can be time-consuming to determine the extent of the error in terms of the impacted columns and rows.

In this article, I will share an easy, automated, and scalable method to quickly summarize and visualize the extent of data errors. The following steps, supported by code snippets and a sample file, will guide you through this process:

Stage 1: Retain All Records Containing Errors

1. Right-click on the query showing the error and select either “Reference” or “Duplicate”.
2. Select all columns and click the “Keep Errors” option in the “Keep Rows” dropdown in the “Reduce Rows” group on the “Home” tab of the ribbon.
3. Select all columns again and click the “Replace Errors” option in the “Replace Values” dropdown in the “Any Column” group on the “Transform” tab of the ribbon. Replace all errors with a unique custom string, such as “my_error”, which will be used to count the errors. (You might need to change the type to text before implementing this step.)

After completing Stage 1, you will have a mechanism to extract all rows causing the error. However, the challenge of determining the extent or number of columns impacted by these errors remains. Stage 2 will address this issue.

Stage 2: Obtain a List of Columns with Error Counts

1. Create a new query and reference the table from Stage 1.
2. Use “Table.ColumnNames” to get a list of columns in the table.
3. Use “Table.FromList” to convert the list of columns into a table.
4. Add a custom column to this table that retrieves the column name from the respective rows and fetches the error count in that column from the referenced table.
5. Sort the table in descending order to highlight the columns with the most errors.

I hope this guide helps you manage and visualize data load errors in Power BI more effectively. Feel free to reach out for sample pbix file for a quick start guide.

[Anonymous]

Hi @aviral ,

This would really provide some help and inspiration for managing data when it encounters problems with loading errors. Thanks for your contribution to the community.

Best Regards,
Adamk Kong