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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
juniorc
Regular Visitor

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

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.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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