Finding errors in Dataflows or PowerQuery

Have you ever had refresh errors in your Power BI Dataflows and had trouble finding them?

Here is a small script that will tell you exactly where the errors are. Add this script to the end of your transformation protocol either in Dataflows or PowerQuery to see which rows are causing the problem.

Make sure to add the previous transformation step in the first line of code (at the end)

GetRowNumber = Table.AddIndexColumn(#"ENTER PREVIOUS TRANSFORMATION STEP", "RowNumber", 1, 1, Int64.Type),
FindErrors = Table.SelectRowsWithErrors(GetRowNumber)
in
FindErrors

This is a great alternative to the otherwise tedious task of finding rows causing errors. If we refresh a Dataflow with errors, we get very broad error messages like below. It will be hard to find which column or row is causing issues.

In the example below, a dataset with 60.000 rows and eight columns we need an automated solution for finding errors.

The script only returns rows that are producing errors and also adds a column showing the respective row number (last column).

Especially if you are working with large Excel sheets we hope this will speed up your hunt for errors.

Please also remember that when e.g. a VLOOKUP in Excel is producing an error, we will get an error message like above (highlighted yellow - Invalid cell value...). Even if it is a text column, it will create problems in Power BI.