Forum Discussion
PBI Desktop and SharePoint List Refresh Error
Thank you for reading my post and helping solve this issue. There are other related threads however I could not find any that address my particular issue.
My report uses two different SharePoint lists as data sources. List 1 is located on a parent site and has over 30,000 list items and while we have stopped adding items, we are still updating columns. List 2 is located on a subsite and is just over 5,000 list items with new items getting added daily.
List 1 is synced to the report with implementation 1.0 while List 2 was using 2.0. View mode is set to All - Retrieve all columns from a Sharepoint List. Privacy is set to organization.
Once List 2 reached 5,000 items the sync stopped working. To be clear, List 1 is not experiencing any sync or refresh issues.
This is the error message we are receiving:
Web.Contents failed to get contents from 'domain/site/list'/RenderListDataAsStream' (500): Internal Server Error
We have been using this report and updating daily since August 2025. Other than List 2 surpassing 5k items, there were no changes to either list nor were there any changes to the parent or subsite. I have tried the following:
- adjusting the implementation to 1.0 on List 2 in the current and in a whole new report;
- removed it and added it back in the current report;
- verified privacy settings are correct for both the SP List and PBI Report;
- had mulitple users attempt data refresh; and
- IT completed a systems health check.
Good morning. Yes I have it working now. These are the steps used for the solution.
Key Recommendations:
1. We indexed the vast majority of columns in a custom view.
2. Using implementation 2.0 we did not have the option to select a specific view. We could only select 'All Columns' or 'Default View'. Regardless of what we selected as the default, it would not work. So we went with implementation 1.0 and it synced.
3. Upon connecting to the data source we deleted all unncessary columns in the third step. 1st - Source, 2nd - Navigation, 3rd - Remove Columns. This helped greatly reduce loading times and obtain the data.
We verified all permissions were correct and expanded the cache memory to the highest it could go. Not sure what help those things did but we were kind of throwing spaghetti at the wall.
10 Replies
Hi RGonz1215
Although SharePoint supports lists with more than 5,000 items, the List View Threshold of 5,000 items still applies to non-indexed queries. Once your second list exceeded this limit, Power BI started issuing a query that SharePoint could not process efficiently, resulting in a generic 500 Internal Server Error.The fact that the first list works correctly with more than 30,000 items strongly suggests that it is better structured, most likely with properly indexed columns or fewer complex fields. The issue is therefore not related to permissions, users, or system health, but to how the list is queried.The most robust solution is to index the necessary columns and reduce the number of columns retrieved by Power BI. In most cases, this fully resolves refresh failures once a list exceeds 5,000 items.Key Recommendations:
1. Index critical columns in SharePoint
Ensure that columns used in filters, relationships, or sorting are indexed in the second list. This is the most important and effective solution and is officially recommended by Microsoft for large lists.2. Avoid retrieving all columns
When connecting to the SharePoint list in Power BI, do not use “Retrieve all columns”. Instead, select only the columns required for the report. This reduces query complexity and avoids evaluation of non-indexed columns.3. Apply filters as early as possible
If using Implementation 1.0, apply row filters at the earliest step in Power Query to reduce the number of items SharePoint must evaluate.4. Use a dedicated SharePoint view
Create a specific SharePoint view for Power BI that:- Uses indexed columns only
- Includes a limited number of columns
- Applies simple filters
Then connect Power BI directly to this view.
I never considered all of this! You're a genius! Ok, I'll give it a shot now and come back with an update.
Hi RGonz1215,
Could you let us know if the issue is now resolved or if you are still experiencing any problems? Please feel free to provide an update if you need further assistance.
