Microsoft has just released a new update to Power Query with a few new enhancements to improve the loading of data into your Power Pivot models.
One of the best features in the update is improved error reporting and debugging. In the older versions of Power Query, if you loaded up a million rows into your model and a few rows failed (typical scenario was type conversion errors such as someone putting “None” into a column that should be a number), it was very difficult to track down the failing rows. This has now been addressed in the latest update.
Now you can see a list of every row that created an error with a specific message to explain why the row failed the load process.
In addition, you can now set a default replacement value for any value that generates an error on the load for each column.
For example, if I had a CSV file that had “N/A” stuffed into a numeric column, I could change this value on load to 0 instead of removing the row (or another appropriate value).