Errors when loading data

[ktt777]

Hi 

I received multiple errors when i want to load data into my power bi :

OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E

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

Object refererence not set to an instance of an object 

I try to replace #REF! with blank , but it does not work

Do you have any idea how to sove ?

Thanks 

[amitchandak]

@ktt777 , Open Data Transformation. Do you see a detailed error? Else check for any column that is showing error. Change its data type to text and see if it works.

Or Look at the source for error in data type like date, the number no getting the required values or getting empty space or special character. Find and correct those.

[parry2k]

@ktt777 what is your data source? Is it excel?

[parry2k]

@ktt777 pretty much the same solution that I posted a few years ago at this post.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ktt777]

i tried to replace #N/A and #REF! by null but it does not work 

[parry2k]

@ktt777 try to do this replace step before change type step.

or you can also do replace errors on the column.

[ktt777]

thank you. But it did not work for me 😞

[ktt777]

does it work if i replace values like above ?

I tried to do some test, sometimes power bi will load data, #NA and #REF! will automatically replaced by null.

why in this case, the data can not be loaded ?

thanks

[Anonymous]

HI @ktt777,

I guess this may be related to your connector and corresponding data driver, what type of data connector and which version of the data provider and driver are you test?

How to Get Your Question Answered Quickly 

In addition, any modification applies to your data source? As the following document mentioned, #ref issue normally appears if a reference error occurred. I'd like to suggest you double-check your source data to prevent this issue.

What are #REF Excel Errors? 
Regards,

Xiaoxin Sheng

[parry2k]

@ktt777 can you share an excel file that you are getting error for loading and I will take a look. Remove sensitive information before sharing.

[ktt777]

Yes, it is excel