Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

0

Power BI refresh data get 500 internal server error

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? 

Status: Delivered

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

 

Comments
v-yetao1-msft
Community Support
Status changed to: Needs Info

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
Frequent Visitor

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"]),

 

henryzf_0-1655868674782.png

 

 

v-yetao1-msft
Community Support
Status changed to: Delivered

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
Frequent Visitor

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.

Lukeg1980
New Member

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
Frequent Visitor

@Lukeg1980 

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. 

 

Lukeg1980
New Member

@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
Frequent Visitor

@Lukeg1980  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
Frequent Visitor

@Lukeg1980 @v-yetao1-msft  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.