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

[Anonymous]

I'm having a hard time successfully connecting my folder in SharePoint to power Bi... It was working perfectly fine until a few days ago. Every time I refresh, I get an error "OLE DB or ODBC error: [DataSource.Error] SharePoint: Request failed" and "OLE DB or ODBC error: [DataFormat.Error] External table is not in the expected format"

Please help, I can't figure out why this's happening... I checked the transformation status of the tables or files and some are marked as "Error" but the file doesn't have any error so I'm not sure how to fix this problem. Has anyone experienced this problem?

[v-alq-msft]

Hi, @Anonymous 

I'd like to suggest you check the related steps Table.TransformColumnTypes in The Advanced Editor. Please check if the data type in the step matches the values in the corresponding column.

Best Regards

Allan

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

[AnkitKukreja]

Hi @Anonymous 

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 check columns of your files in sharepoint which are giving errors in PQ.

Thanks,

Ankit