Importing *.xlsx files works from local drive but not from SharePoint Online

[jhmorse]

I've built a data model using *.xlsx files on my local hard drive. Since they are weekly activity reports from the team, we would like to have the team submit them to a SharePoint Online folder and then have PowerBi pull them in during a refresh. This worked for a few days; however, I suddenly started getting the following error:

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

Why does this work from my hard drive but not from SharePoint?

[v-lili6-msft]

hi  @jhmorse 

This is generally due to change in data format. Like in your data few days ago date format would be different and now something else or maybe date field has (na, - or some special characters) . Similarly, this could be possible if the number or text field has text or a number in that column or vice-versa.

As the error states, your column which you would have gone through (change type) and is in correct format, now has some another format of data which is giving you this error.

Please download the pbix file then open it in power bi desktop, now check each step in The Advanced Editor.  in power query.

and also check if you surely not have any hidden files in a folder which you pull in to your model as a datasource. Damaged xlsxes and any hidden files can mass up your dataflow. (I had those when I tried to save a file on our shared drive and had some network error in the meantime.) Easiest if you check in your query editor the list of files so you can easily spot whether there is something that does not make sense.

Regards,

Lin

[jhmorse]

I finally found the issue. It was a hidden file. This SharePoint site is part of Teams, and someone created (and then deleted) a Wiki page for the site, which created a *.mht file. I went into the step after the Source statement in the query editor and filtered it to only include *.xlsx files.