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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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