SharePoint list 2.0 broke for list over 5000 items...but is working for similar lists? 500 error

[john_colvin]

I'm using Power BI to query a SharePoint list, and I'm using the 2.0 implementation of the SharePoint connector to do so. This was working fine until my SharePoint list hit 5000 items, but now I'm receiving a "500 internal server error" when trying to run the query. I don't understand why I'm getting this issue, because on multiple other SharePoint lists with over 5000 items, the 2.0 implementation is working fine. What could be the issue?

Note: Default view contains mostly text fields, one choice field, and 4 person fields (one multi-person). 19 total fields in Default view. The list has 11 indexed columns.

Alternate approach: Tried using REST call (with pageToken) to SharePoint instead, and can now get all results, but have lost the ability to schedule refresh.

Really need ability to schedule refresh of this data...any ideas, please help!

[lbendlin · Accepted solution]

Tried using REST call (with pageToken) to SharePoint instead, and can now get all results, but have lost the ability to schedule refresh

That (plus $batch) is generally the recommended approach.  Make sure you use RelativePath and Query options in your Web.Contents  call.

[john_colvin]

@lbendlin, thank you kindly for the response! I could use a little more guidance if you can spare some time...perhaps you could send me an example, as I'm somewhat new to these functions. See below for a sample of what I'm doing in trying to apply your advice about RelativePath and Query Options. I'm getting an "invalid token" error...the editor doesn't seem to like the dollar sign symbol.

Also I have no idea how to apply $batch in this context, even after a bit of documentation review.

Again, thank you for the response!

[lbendlin]

you can escape these characters

$top => #"$top"

[john_colvin]

@lbendlin Thanks! I was able to escape the characters, but now I receive an error for the WebContents function as below.

"Expression.Error: 3 arguments were passed to a function which expects between 1 and 2."

Looks like this is occurring because I'm passing the base url in the first argument, [RelativePath + Query Options] as second argument, and [Headers=[Accept="application/json"]] as my third argument. Can you provide guidance for resolving this, or maybe point me toward some documentation?

[lbendlin]

Example 1

Web.Contents - PowerQuery M | Microsoft Learn

[john_colvin]

Perfect! That's what I needed. Thank you again. Just to confirm, once I use RelativePath and Query Options, should I be able to use scheduled refresh on the dataset, or are there other steps required? If so, I will mark your initial response as the solution.

[lbendlin]

correct.