With no changes made to the source SharePoint list my report refresh started failing this morning with the error below. I've had this happen on a different list and never found a fix for it.Error:DataSource.Error: Web.Contents failed to get contents from 'https://sharepointed.sharepoint.com/sites/fun-stuff/_api/Web/Lists(guid'9ab1335c-b8fe-1b99-be01-77d5f2c3329c')/RenderListDataAsStream' (500): Internal Server ErrorDetails:DataSourceKind=SharePointDataSourcePath=https://sharepointed.sharepoint.com/sites/fun-stuffUrl=https://sharepointed.sharepoint.com/sites/fun-stuff/_api/Web/Lists(guid'9ab1335c-b8fe-1b99-be01-77d5f2c3329c')/RenderListDataAsStreamConnection steps:letSource = SharePoint.Tables("https://sharepointed.sharepoint.com/sites/fun-stuff", [Implementation="2.0", ViewMode="All"]),#"9ab1335c-b8fe-4b23-be01-88d5f2c3320c" = Source{[Id="9ab1335c-b8fe-1b99-be01-77d5f2c3329c"]}[Items]in#"9ab1335c-b8fe-1b99-be01-77d5f2c3329c"

Nope, opened a ticket with Micorosft Premier support and they said it's a SharePoint issue. SharePoint support said nothing can be done. a lot of wasted time..I thought the point of using the Power BI 2.0 connection to SharePoint was to leverage paging and avoid issues like this.

Error Connecting to SharePoint List (500): Internal Server Error

24 Replies

DaFloDo
Resolver I
3 years ago
we had the same behavior on one of our lists, that had 20k items long time woking an suddenly did not work anymore.
We found, that there was an aggregation (count) set in the default view of the list. So even, if you use the "all" setting, which should ignore all view settings, the aggregation setting of the default view seems to result in an error.
So for us, removing the aggregation from the default view of the list, solved this issue.
- Ola_G
  Frequent Visitor
  3 years ago
  Hi, can you help me with this "removing the aggregation from the default view of the list" - how could I do it?
  Thanks!
  - DaFloDo
    Resolver I
    3 years ago
    hi Ola_G,
    in the SharePoint list, in the upper right of the list, select the default view and then click on edit current view.
    
    if there is an aggregation for any of the columns, set it to "none". This was fixing the problem for us.
    Don't forget to save these changes
- Ayush_tiwari08
  Helper I
  2 years ago
  This Solution worked for me thank you so much Sir for your help here.
- Anonymous
  Not applicable
  1 year ago
  This worked for me. Thank you so much!
olsen9
Frequent Visitor
1 year ago
My issue was that someone put a count in the default view under totals. PowerBi does not like this
V-lianl-msft
Community Support
4 years ago
Hi Anonymous ,

If you have multiple queries and when refreshing, Power BI evaluate and refresh all queries at the same time.

This may be the cause of this error.

Please try turning off the parallel data loading in the options for data load.

That will then not load the data in parallel but rather one at a time.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  4 years ago
  Cleared cache(s)
  Updated the setting you mentioned
  Createad a new report
  
  Source:
  SharePoint Online List
  2.0 option
  Selected the list
  
  Same error noted above.
  
  The 2.0 Implementation must be used due to the size of the SharePoint list.
  - Anonymous
    Not applicable
    4 years ago
    Hello, im with the same error.
    I noticed that the error started after the sharepoint list had more than 5k items.
    did u find any solution to this error?
    in my case i need to switch conection too 1.0 and rebuild the views.
wodraeve
Frequent Visitor
4 years ago
I am experiencing the same issue. Did anybody ever find the solution? Thanks
- Anonymous
  Not applicable
  4 years ago
  Nope, opened a ticket with Micorosft Premier support and they said it's a SharePoint issue. SharePoint support said nothing can be done. a lot of wasted time.
  .
  I thought the point of using the Power BI 2.0 connection to SharePoint was to leverage paging and avoid issues like this.
PhilD
Frequent Visitor
3 years ago
You're kidding!! Am I to understand the Power Bi literally just stops working when a SharePoint list crossed the view threshhold and there is no solultion?
NunoMota
New Member
7 months ago
I don't think the issue is only limited to the fact the list has gone over 5000 items. I have one Power BI report that collects data from 5 different SharePoint lists, all using v2 of the connector. Two of these lists have over 50,000 entries, with around 20 columns, most with data, so they are huge lists. Power BI has no problem retrieving data from these lists.

Yesterday, one of the other lists went over 5000 entries, and now Power BI is complaining... I checked everything, and all lists are being retrieved in an identical form: using the same v2 connector, no filters, nothing...

I literally have no idea what the problem is or how to fix it, but having two huge lists syncing without a problem tells me it is possible to get this to work!
- DaFloDo
  Resolver I
  7 months ago
  NunoMota did you check for aggregates on a column (count, sum, ....) in the SharePoint default view? this could be the reason for this error. If so, just remove the aggregate.
  - NunoMota
    New Member
    7 months ago
    Good point DaFloDo, I wasn't aware of that! I checked and there is none. I don't think I ever used that to be honest. But thank you for the suggestion.
    
    Updating the query from:
    = SharePoint.Tables("https://site", [Implementation="2.0", ViewMode="All"])
    to:
    = SharePoint.Tables("https://site", [ApiVersion = 15])
    
    Seems to have worked though! However, the others with 50k+ items are not configured like this... Very strange!