Why do I get a DataFormat.Error when data source is Sharepoint?

[CMMkamil]

Hello,

I'm relatively new to Power Bi and I'm currently playing around with it trying to teach myself how to use it. 

I came across a problem where If I go to refresh the data I get the following error message...

"OLE DB or ODBC error: [DataFormat.Error] External table is not in the expected format."

The Excel spresheet I use is a simple spredsheet with around 10 columns, standard text and numbers, no formulas, no specific formatting and was created from scratch and not exported from any third party system.

I use the 64-bit version of Power Bi desktop, I have a 64-bit operating system and I downloaded the 64-bit version of AccessDatabaseEngine. 

Interestingly when I use the exact same spredsheet which is saved directly on my pc and use the data source as Excel I don't get the error. It's only if I use Sharepoint as the data source so not sure if this has anything to do with the error.

I have also noticed that when I open up my data source and view the data an extra row has been added and each cells shows 'error'. When I go to delete it, it shows the following error...

"An error occurred in the 'Transform File' query. DataFormat.Error: External table is not in the expected format. Details: 6f4e8d20-c6aa-40ae-ad6c-f6c92ff466ca.XLS."

I have gone through pretty much all the threads relating to this however, non of them solved the issue. 

Any help will be much appreciated. 

Many thanks

[v-yingjl]

Hi @CMMkamil ,

You may use the early versions of Excel workbooks (.XLS files of type Excel 97-2003), it will cause problems sometimes.

You can try the latest version of Excel and open .XLS file, save it as .an XLSX file to try again.

Refer the Microsoft document:

https://docs.microsoft.com/en-us/power-bi/desktop-access-database-errors

Best Regards,

Yingjie Li

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

[CMMkamil]

Hi @v-yingjl,

Thanks for your response however, I have already gone through this link and the document is saved as .xlsx

Regards,

Kamil

[nmasood90]

Hi @CMMkamil  did you ever find a solution? I am facing the exact same issue.

[CMMkamil]

Hi @nmasood90 

Unfortunetly I haven't yet, sorry.