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.
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.
Can you replace the #N/A in power bi / query editor? I have tried with replace values and replace errors and it didn't worked. thanks.
I agree, fixing the data doesn't seem to be the right answer. We should be able to resolve the bad data within the query.
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.
Yes, excel is the datasource. I just found 10 rows with #N/A values.
Removed the #N/As and it worked! Thank you!!!
I have the same issue but none of the suggestions seem to resolve it. Even removing rows with errors
