Power BI refresh data get 500 internal server error

[henryzf]

datasource: sharepoint list 

Power BI desktop use sharepoint online list 2.0 to get the data.

When sharepoint list itmes over than 5000. the power bi couldn't refresh. as below error

Web.Contents failed to get contents from ' (500): Internal Server Error. 

is ther any idea on this issue? 

[Anonymous]

Hi @henryzf 

Where are you refreshing to get this error, Desktop or Service ? Can you provide a screenshot of the error ? 

Best Regards,
Community Support Team _ Ailsa Tao

[henryzf]

Hi ,

It both got error on desktop and Power BI service.  and once I removed sharepoint list items less than 5000 rows and then it can be refresh again. 

and I noticed that the issue happend on datasource connecter is sharepoint online list, 2.0.  1.0 or sharepoint list is no problem.

DatasourceConnecter: sharepoint online list, 2.0

Source = SharePoint.Tables("https://XXXXXXXXX", [Implementation="2.0", ViewMode="All"]),

[Anonymous]

Hi @henryzf 

The reason why Microsoft placed the 5,000 limit threshold — to maintain the performance of the server and enable users to access the library without waiting for hours. You can get a better understanding of SharePoint 5,000 Item Limit Threshold by following this article .

https://www.mrsharepoint.guru/resolve-the-sharepoint-item-limit-threshold/ 

Best Regards,
Community Support Team _ Ailsa Tao

[henryzf]

but why get the data from sharepoint online list 1.0 instead of 2.0 is working fine? the same list, 1.0 is working well over 5000 rows even 22000 rows I tried it. but 2.0 doesn't work over 5000 rows.

[Anonymous]

I am also experiencing the same issue for one of my lists. This was previously working prior to hitting the 5k item count. Since then it seems to work sporadically if I make changes to the view. 

I have other lists which are well above 5k and have been for some time. Their list settings are identical and the connection within PowerBI are also identical. Yet PowerBI can connect to these lists without an issue. 

There seems to be no consistency here. Could someone shine some light on this please?

[henryzf]

@Anonymous 

I opened a ticket with MS support and not yet have any solution or workaround for this case.

I want to add something on this case:

1, This issue currently looks like only occured on sharepoint online list 2.0 connector. 1.0 or sharepoint list is ok for Power BI datasoucre.

2, The issue is not happend on the list items once hitting the 5k. for my scenario, one of my list is reach to 22000 itmes then Power BI starting refresh failed with 500 internal server error.  and antoher list just reach to 13000times then got the refresh issue.

after that I have to delete the items below than 5K, Refresh worked again and once hitting the 5K the refresh failed again. 

[Anonymous]

@henryzf  Thanks for the advising the case is with MS.

I have also played with connection 1.0 and found that it will connect with lists greater than 5000 items. However the retrieval time is significantly affected by downgrading the connector. 

Lets keep each other updated!

Cheers, 

Luke

[henryzf]

@Anonymous  you are totally correct. 2.0 connector it only take 5 or 6 mins when refresh on Power BI service with more than 10000 items, but 1.0 it will take almost 15mins refresh on Power BI service with less than 5000 items.

[henryzf]

@Anonymous @Anonymous  MS Support called me today that stated this is sharepoint list limited issue so currently there is no way to fix it. looks like sharepoint online list 1.0 connector is the only option for Power BI refresh data over 5K items.