Just like any other language, Power Query has its own different types of errors, one of them is DataFormat Error.

Dataformat errors in Power Query are mostly data type errors and do not prevent you from loading data to Power BI, instead the cells will be loaded as blank. The screenshot below shows 3 errors for 78 records when the data is loaded to Power BI.

To audit the error or get more details, do the following:

1.  Click on View errors from above to go back to Power Query Editor
2. In Power Query Editor-> Select View tab -> Check Column Quality

For this example, UnitsInStock has 3% error for all rows in that column.

3. Click on Kept Errors under Applied Steps to see only rows with errors, the Kept Errors step is automatically added when you click on View errors from step 1.

Below screenshot show 100% error for the 3 rows.

4. To see details of the error, select the error as seeing below:

For this example, the error occurs because the column data type is Number, but 3 rows in the column has Text values, Power Query return error because it cannot convert the Text values to Numbers.

DataFormat.Error: We couldn’t convert to Number

5. To audit the error, create a custom column using the try expression, from Add column -> Custom Column -> type the expression below. A new Record column will be created.

6. Click on Expand -> Select Error

7. Click Expand again -> Click OK

You will see the Reason, Message and Detail of the error. You can load the error details to Power BI and create an Audit report if needed.

Also, you can use this report to make changes to the affected values at Data source level and reload to Power BI.

8. To replace the errors in Power Query from Advanced Editor, add a line of code to the MQuery with the value to replace with.

For this example, I am replacing all errors with 0. Optionally, you can replace the errors by right clicking the column, then select Replace Error.

Note: Those new values from Power Query will not be updated to your source data.

9. Click on Enable Load to load the data to Power BI if its not enabled by default, right click on the query from the left and select Enable load.

10. Click Close and Apply

The data will now load and apply all changes successfully without errors.

Happy Auditing! 🙂