Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sanjica
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
parry2k
Super User
Super User

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

15 REPLIES 15
sipadip
New Member

Ill share my issue that lead me to this error and my solution:

 

I did a power query from a spreadsheet. I filtered everything down to what I wanted but was left with "error" cells and nothing loading into the new spreadsheet. I tried to remove and replace the "error" cells to no avail. Eventually I figured out that my filtering was what was giving me grief. I went back and removed the "error" cells before I did the filtering and got the results I was looking for. 

 

Hope this helps!

simonparnell
New Member

There is a much better solution than removing #N/A from the data source.  Use Transform Data - Select the Table with the error then Right click the column header and select Replace Errors, you can then modify the erronous cells to whatever you would prefer to see in them - I use null or 0 dependant on the data held therein.   NB: Only use Remove Errors if you are happy for the entire data ROW with that error in it to be removed.

clanning345
New Member

My problem is that I'm pulling in multiple spreadsheets and I cannot find the cell that is causing the issue. Is there a worksheet search to find bad cells?

Fier
Regular Visitor

I can not clean nor transform the data when I'm using somebody else's DataFlow. Therefore I used the following formula to create a numeric value for the field "Score":

ScoreVal = if(exact([Score],"#N/A"), 0, value([Score]))
Anonymous
Not applicable

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.

This works perfectly! 

 

Thank you.

Aldrannt
New Member

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.

AndreDinis
Frequent Visitor

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

parry2k
Super User
Super User

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

I have the same issue but none of the suggestions seem to resolve it. Even removing rows with errors

 

RobG123_0-1701425402380.png

 

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.

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. 

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.

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

 

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.