OLE DB or ODBC:[DataFormat.Error] cell value #REF invalid

[efroseroc]

Hi thank you for reading,

I've been using this model for 6 months now but suddenly I get this error.

My datasource is an Excel file, so logically I looked thoroughly for any #REF errors but found nothing.

I checked the quality of the columns and found 0 errors in Power Query

I don´t really know what to do,

Any help is appreciated,

Thank you,

[Syndicate_Admin]

Thank you I was very useful your contribution.

[Syndicate_Admin]

What happens when your consuta is done from several files? That is, the only way is to enter each file and do what you mention?

[v-xiaoyan-msft]

Hi @efroseroc ,

Glad that your problem is solved, thank you for sharing. Would you mind marking the correct answer as solution, so that other users with similar problems to yours can find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC]

@efroseroc 

In Power Query, can you go to the respective query, go to the most recent / last Applied Step (far right pane).  Keep a look out for an error notification at the base of Power Query and start to click on each step in the Applied Steps section as you go back to the first one being "Source".

If the issue is not identify, then check the Source file and ensure you have no changed the Name, File Location, File Type (i.e. CSV, XLS, XLSX, XLSB, XLSM, etc), Field / Column Headers, etc.  

If the above doesn't identify the issue, check the Excel file and ensure that no changes to Data Types in the file itself have been made. 

Finally, if the above doesn't identify the issue, recreate your Excel file and save it does as a new XLSX. Adjuste the "Source" step in your "Applied Steps" to ensure it links to the new file.

This should resolve the problem. If not, I have nothing else unfortunately! 😞

[efroseroc · Accepted solution]

Hi @TheoC 

Thanks for your kind answer, it was simpler than we thought, but still I leave it here in case anyone finds it useful: if you Ctrl+F  or (Ctrl+B) in some places, and search for #REF excel will not find those values.  You have to go to Find and select, then go to special and enable errors.

Rookie mistake but what can you do.

Thanks again!