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

[Taz_Car]

Hello All,

I am getting the following message

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

I am importing from excel sheets in Sharepoint.  I can't find a #value error in the source files but it's complex so I might be missing it.  I have tried replacing the error, but this makes no difference.  Can anyone point me in the right direction?

[tertill28]

https://stackoverflow.com/a/60234320/22669679

"If you are sourcing from an Excel Spreadsheet, just after you import the data, before you even Promote Headers or Change Data types select all columns and delete errors.

That worked for me."

This worked for me too!

[xyghttfhoo]

Perfect, worked for me as well (as per 14jan25)

[SiCo]

This worked today 3rd Oct 2024. Didn't work when removing errors later in the query list. Thank you for this post!

[paulberaud]

Can confirm this worked for me as of 11-6-2023

[Anonymous · Accepted solution]

Hi @Taz_Car ,

This error is thrown in Excel, where Power Query reads its data as a value (reserved word), not the formula itself.
The suggestion is to go back to the data source (excel) and replace VALUE with another meaningful name.

Related thread:
Solved: Re: "OLE DB or ODBC error: [dataformat.error] inva...

Fix DataFormat.Error: Invalid cell value '#NAME?'

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Taz_Car]

Thansk I did that.  It took a while to ID the error as there are multiple feeder sheets but the issue is resolved. 

[lbendlin]

You can choose to remove the rows with errors or you can replace the errors with something meaningful.