cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sanjica Frequent Visitor
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
Super User IV
Super User IV

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 Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

8 REPLIES 8
Super User IV
Super User IV

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 Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Sanjica Frequent Visitor
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!!!

 

 

ACTR New Member
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.

jgrachie Frequent Visitor
Frequent 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.

AndreDinis Frequent Visitor
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

Aldrannt Occasional Visitor
Occasional Visitor

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.

Jtb790 Frequent Visitor
Frequent Visitor

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.

radio_freq New Member
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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors