top of page

Finding errors in Dataflows or PowerQuery

Updated: May 26, 2023

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)

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.

2,416 views3 comments


Apr 28

Thank you!


Frank Cerullo
Frank Cerullo
May 25, 2023

I think Case matters, GetRownumber should be GetRowNumber

May 26, 2023
Replying to

Great catch, thank you for pointing this out. No idea how that happened...

I changed it

bottom of page