"OLE DB or ODBC error: [dataformat.error] invalid cell value '#NAME?'

[MikeO]

I have connected to a single table in excel and whenever I try to close and save the query editor, I get the above message.  In reviewing the table in query editor I see the bottom row is all errors; however, when I try to delete the bottom row the entire table goes away and the same error message appears in the query editor.

Any suggestions on how to correct this would be appreciated.

[djankuna]

Hi! 

Same issue from our side. I am trying to refresh Power BI (updated excel sheet uploaded), it says:

OLE DB or ODBC error: [DataFormat.Error] Invalid cell value '#NAME?'...

But I cannot track this mistake in my excel sheet, nor check this in Power BI query edditor (because the excel is not uploading/refreshing - old format data is 'standing'). 

any suggestions here? 

[cpolex]

Facing same issues here.  It happens when i try to expand the table after merge query. Everything works well before that.. even after an append query.

Tried replacing error with values and removing empty rows etc but does not solve the issue.

Appreciate any guidance to troubleshoot the issue and hopefully fixing it at power query

[dkay84_PowerBI]

In addition to Marcel's comment, have you tried removing errors as a step in your query editor flow?  If that doesn't work, you can try filtering, and lastly add a new column that checks if the value is error return null otherwise return the original value.  Then use that column (and remove/filter the blank row if needed).

[MarcelBeug · Accepted solution]

My suggestion would be to correct the error where it originates: in Excel.

[ivanac01]

really not useful. I design the report and 10 other ppl edit the excel files that I only had read access to. There must be a way to resolve this in power bi