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)
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.

2,732 views3 comments

3 Comments


Guest
Apr 28

Thank you!

Like

Frank Cerullo
Frank Cerullo
May 25, 2023

I think Case matters, GetRownumber should be GetRowNumber

Like
Brunner_BI
Brunner_BI
May 26, 2023
Replying to

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

I changed it

Like
bottom of page