Data source error (500) spreadsheets in Teams folder

[Steph_Aust]

In the last two weeks i have had this error come up on a scheduled refresh. Dataflow connects to the same Teams (SharePoint) folder and pulls information out of 13 spreadsheets. In the dataflow i create 3 tables from these spreadsheets and then combine them. I have checked the error messages and each time it is failing to connect to a different file and errors on different tables.

If i go into the query i can step through each of the table queries without problems. Even loading up all the data. I have looked through some of the other posts with similar errors and it doesn't look like the same thing. Any ideas what is going wrong?

Error: Data Source Error : DataSource.Error: Web.Contents failed to get contents from 'https://XXXX/_api/web/getfilebyserverrelativeurl('/teams/XXX/Spreadsheet.xlsx')/$value' (500):
Internal Server Error DataSourceKind = <ccon>SharePoint</ccon> DataSourcePath = <ccon>https://xxxx/teams/xxxx</ccon> Url = <ccon>https://xxxx/teams/xxx/_api/web/getfilebyserverrelativeurl('/teams/xxxx/Spreadsheet.xlsx')/$value</ccon>. RootActivityId = 2d51ea3b-3dae-4436-b32e-a202c703dc65.Param1 = DataSource.Error: Web.Contents failed to get contents from 'https://xxx/teams/xxx/_api/web/getfilebyserverrelativeurl('/teams/xxx/Spreadsheet.xlsx')/$value' (500): Internal Server Error DataSourceKind = <ccon>SharePoint</ccon> DataSourcePath = <ccon>https://xxx/teams/xxx</ccon> Url = <ccon>https://xxx/teams/xxx/_api/web/getfilebyserverrelativeurl('/teams/xxx/Spreadsheet.xlsx')/$value</ccon>

[lbendlin · Accepted solution]

Your sharepoint instance is overloaded. Speak to your Sharepoint admin to figure out why.

[lbendlin · Accepted solution]

Your sharepoint instance is overloaded. Speak to your Sharepoint admin to figure out why.

[Steph_Aust · Accepted solution]

Thank you, this has lead to me to the solution. SharePoint will only support views with 5000 items. I found that my SharePoint Library was keeping 500 versions. Since I have 12 spreadsheet this now exceeds the 5000 item limit. Even though I am not using all the versions in my query it was getting stuck on the step wehre I was filtering to the most recent version. Reducing the major version kept to the smallest number allowed (100) has fixed the problem.