Forum Discussion

RGonz1215's avatar
RGonz1215
Regular Visitor
4 months ago
Solved

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 l...
Need Help
  • RGonz1215's avatar
    RGonz1215
    4 months ago

    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.

     
Zanqueta's avatar
Zanqueta
Super User
4 months ago

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.
     
     

 

RGonz1215's avatar
RGonz1215
Regular Visitor
4 months ago

I never considered all of this! You're a genius! Ok, I'll give it a shot now and come back with an update.