Forum Discussion
Error Connecting to SharePoint List (500): Internal Server Error
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.
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!
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.
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!
Thank you! Thank you! Thank you!
I never once thought the aggregates would have anything to do with it. This solved my issue 🙂
