cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DataFormat.Error: Invalid cell value '#N/A'.

I'm trying to pull in a value from another table using merge. I'm getting the following error: DataFormat.Error: Invalid cell value '#N/A'.

 

I've looked at other forums to determine what is causing the error, but they haven't been helpful. 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

11 REPLIES 11
Highlighted
Super User VII
Super User VII

Re: 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Frequent Visitor

Re: DataFormat.Error: Invalid cell value '#N/A'.

Yes, excel is the datasource. I just found 10 rows with #N/A values. 

 

Removed the #N/As and it worked! Thank you!!!

 

 

Highlighted
New Member

Re: DataFormat.Error: Invalid cell value '#N/A'.

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.

Highlighted
Regular Visitor

Re: DataFormat.Error: Invalid cell value '#N/A'.

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.

Highlighted
Frequent Visitor

Re: DataFormat.Error: Invalid cell value '#N/A'.

I'm having the same problem but I can't edit the excel source. I've tried to replace the '#N/A' for other values in the power query editor but with no success.. Is there any other way to solve this in power query editor?

 

Thank you,

AD

Highlighted
New Member

Re: DataFormat.Error: Invalid cell value '#N/A'.

Power Query in Power BI and Excel simply does not support values that represent errors such as: "# N / A", "# N / A"

I lost a lot of time, everything to find that you have to look for them manually in the table, in my case in the excel source, it is useless sometimes to filter and try to detect these values in the search list, I had to check the entire column to search and delete them.

 

Power Query en Power BI y Excel simplemente no soporta los valores que representan error tales como: "#N/D", "#N/A"

He perdido mucho tiempo, todo para encontrar que hay que buscarlos manualmente en la tabla, en mi caso en la fuente excel, es inútil a veces filtrar e intentar detectar estos valores en el listado de valores, yo tuve que revisar toda la columna para encontrarlos y borrarlos.

Hope it works for you. Hope MS could care soon.

Highlighted
Advocate I
Advocate I

Re: DataFormat.Error: Invalid cell value '#N/A'.

Transform > Replace Errors should do the job of cleaning #N/A from a single column or an entire table. 

 

Would consider this best practice when inputting data from Excel, I haven't tried but I would expect this to collect other Excel failure defaults such as #Value errors.

Highlighted
New Member

Re: DataFormat.Error: Invalid cell value '#N/A'.

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.

Highlighted
Frequent Visitor

Re: DataFormat.Error: Invalid cell value '#N/A'.

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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors