Invalid cell value '#N/A'.. An unexpected exception occurred (Replace Errors appears not working)

[juniorc]

Hi, Community,

I got data from an Excel file placed in a SharePoint, and the result I need is to load it from the Power Query Editor to Pivot and create visuals. However, when I close and apply it, I have a failing message to Load it, stating, "OLE DB or ODBC error: [DataFormat.Error] Invalid cell value '#N/A'.. An unexpected exception occurred".

Looking at the Excel file, I identified several rows with the "#N/A" value in numerous columns (these values do not come from calculations.). In Transform, Any Column group, I used the functionally Replace Errors using Value "null" for the entire table, but it didn't work, and I'm not allowed to change the Excel file.

Obs.: this is my first time posting in the community, so my apologies if any information is missing. I'll be happy to provide it.

[Anonymous · Accepted solution]

HI @juniorc,

The power query table does not support '#N/A' as null values. Current one data field except 'any' type only supports storing one data type.
If your fields include multiple types of values, they will show the error of 'not able to transform data into specific data types'.

I'd like to suggest you add a replace function to check all the data fields to replace the NA to null to prevent this issue.

Regards,

Xiaoxin Sheng

[LordLewy]

Hi
I got similar problem and it was critical to put replace errors step when column is still in "ANY" type - it stop working when I changed type to other one