Forum Discussion
DataFormat.Error: Invalid cell value '#N/A'.
Are you using excel as datasource? Sometime in excel in formulas you have value "#N/A" and that could be the root cause for this. You need to replace that value using query editor with "null" or space and I guess that will do it.
When replacing #N/A use the replace errors option and replace with the word null. It doesn't do anything if you leave the replacement blank like it normally would in Excel or other programs but it doesn't really give you any indication that blank isn't doing anything.
In my case, I was developing a dataflow, and got a similar error except the excel file had a row that included '#Name?'. Unfortunately neither removing rows with errors or trying to search and replace #Name? with null worked. Simply removing that particular row however fixed the problem. Not sure why removing rows with errors did not work even though the row was removed in the query editor. And I don't think trying to find a replace '#Name!' worked because that wasn't the contents of the cell, it was [Error]. Anyway, not the ideal solution as I'm now missing a record, but it worked.