Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
john_colvin
Frequent Visitor

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

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!

 

Snag_5146e84c.pngSnag_51471a39.png

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

7 REPLIES 7
lbendlin
Super User
Super User

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.

@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!

 

john_colvin_0-1681310747549.png

 

you can escape these characters

 

$top => #"$top"

@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?

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.

correct.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors