Tracking down the source of a dataformat error in a query

Question

I have a query in Excel, retrieving data from a different workbook. Recently, however, the table the query writes data to has failed with the error DataFormat.Error: Invalid cell value '#N/A'..

Going through the steps of the query in the query editor, it looks like the error happens when some rows used to be sorted out:

I suspect the error is because of a change of some column header in the source file, but I am far from certain if this is actually the case.

Is there any way to track down a more detailed error description, e.g. to get to know what cells it is that has the invalid values? Is there some other way to get a better grip on what is causing the error?

Answer 1

Power Query really doesn't seem to know what to do when it finds an excel error code (#N/A; #REF; etc). Often when working in Power query, there isn't a problem because the row containing the error code isn't actually loaded into the preview data. It isn't until you do an operation like sort, group, remove duplicates, or something that has to read through all the rows in the data set that you run into the problem.

You have to either fix this by using the try functionality in M to handle the error, or fix your source data so that it no longer contains the error code.

Answer 2

if your source is an excel, search for the N/A inside of your workbook for values and replace them with proper values. then it should work.

Answer 3

I overcame this error by inserting a "Remove Errors" step before Filtering.

Answer 4

If you are sourcing from an Excel Spreadsheet, just after you import the data, before you even Promote Headers or Change Data types select all columns and delete errors.

That worked for me.