cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Retrieving data from SharePoint causing throttling errors

I have a report that gathers data from a bunch of SharePoint files. (In fact, I have a lot of reports that gather data from SharePoint files.) However, this report will be working and then suddenly fail with a SharePoint Request Failed (contextinfo). At the same time, if you visit the SharePoint site, (or any site in my tenancy) you get the throttle message. All other throttling effects happen too, like the inability to upload via OneDrive. It appears to me that the Power Bi requests are causing the throttling issue - has anyone else run into this?

 

throttle.png

16 REPLIES 16
Community Support
Community Support

Hi @hansei ,

 

What connector you are using to get the file in SharePoint, SharePoint.Files or Web.Contents? Does it issue occur in every scheduled refresh? Does there are any other dataset try to retrieve from this sharepoint at the same time? What is the last time the refresh success?


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi@v-lid-msft ,

 

I am using the SharePoint.Files method, and no it is not every refresh - the issue was occuring with around 2/3 of refreshes.

 

I am currently building a new dataset which retrieves from the same site (although a different library). It is certainly possible that while I am editing queries, a scheduled refresh is occurring simultaneously. Is this a known limitation?

 

Hi  @v-lid-msft ,

 

As an update, I can reproduce the error in the report I am building while scheduled refresh is disabled. Which is to say, no other processes are accessing the website outside of the occasional user and the OneDrive process.

Has anybody else encountered this? A number of my reports can no longer refresh due to the SharePoint Request Failed - which is esssentially a timeout caused by SharePoint throttling. And I have no control over the manner in which the 2 products operate AFAIK.

Hi @hansei ,

 

Sorry for our delay in response. Will this dataset retrieve huge data from SharePoint? How much time it will take if you refresh it in Desktop?

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

A small amount of data, but a large amount of files. There are potentially 10,000s of files, but only about 500 of them are actually read for contents. Desktop refresh takes 15-20 minutes.

Hi @hansei ,

 

Have you tried to filter the nessary content first before expand the content, such as filter 500 files after use SharePoint.Files / SharePoint.Contents function to get list of the 10k files, it may reduce the connection number of get files if filter a little amout of them first.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes, I don't expand content until I've filtered it. In fact I can reproduce the error by reading just one single file.

Hi @hansei ,

 

If seems does not a limitation on this connector, we also did not find any limitation on SharePoint Site Online. Could you please try to force use the API Version 15 as a workaround?

 

SharePoint.Files(url as text, [ApiVersion = 15]) as table

 

Based on this documentWhen not specified, API version 14 is used. 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lid-msft ,

 

API 15 is already specified, and has been for some time.

This is the limitation that Power BI is triggering. Power BI doesn't report anything other than request failed, but if I visit the site using the credentials used by Power BI, I get the throttle.htm error message.

I have the same throttling problem with PowerBI refreshes.

 

I can reproduce the problem in both PowerBI refreshes or PowerQuery refreshes in Excel.

 

I have narrowed it down to the following condition: When I Table.Combine or Table.NestedJoin on data sources that were different at the outset. eg. SharePoint combined or joined with an Excel table.

 

I haven't found anything that works to improve the situation though.

@Andrew501, thanks for your insight. 

In my case, all sources are sharepoint, although there are a few combines and joins

I have the same issue (https://community.powerbi.com/t5/Service/Avoid-throttling-when-loading-from-SharePoint-folder/m-p/12....
I have some joins in the load. Were you able to work around this limitation?

Not as yet. Project was shelved, but will be returned to. Did you find that the Binary.Buffer() trick worked? I could actually reproduce the problem after opening just a single file.

Frequent Visitor

Have you found a solution for this? 
I'm trying to create a report on a SP list with ~30k items and I keep getting throttled.  I can pull the data into a report but the problems start when I try to change field types or expand columns (Modified by).

Hi @hansei ,

 

It maybe a issue with this connector, But sorry for that, We cannot reproduce the issue here and also not found an effective solution. We suggest you to open a ticket here for help if you are a pro user: https://powerbi.microsoft.com/en-us/support/

 

Support Ticket .gif


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors