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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hansei
Helper V
Helper V

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

19 REPLIES 19
Andrew_Taylor
Frequent Visitor

What is SharePoint throttling?

SharePoint applies an API call limits on users to ensure reliability and performance. SharePoint claims that it is a non-quantifiable API call limit, and when this limit is exceeded it is called throttling.

How do I know I am being throttled?

If you click on a SharePoint link and you get this response, you know you are being throttled:

Andrew_Taylor_0-1626879279847.jpeg

 

How do I know if the throttling is causing my PowerBI refresh to fail or be unreliable?

Good question. I was able to determine throttling was causing the PBI refresh to fail by seeing the above throttle limit URL message while in parallel refreshing a PowerBi report. The error report for the refresh failure was not helpful.

How can stay below the API throttling limit?

Microsoft does not provide any way to measure one’s API call rate, so empirical means are necessary. In my testing, I found that staying below approximately 20 API calls per minute was a safe limit. I define a safe limit as follows:

  • Never experienced throttling while refreshing
  • Able to refresh 2 separate PowerBI projects in parallel and still work in SharePoint as needed

Note: it is important to stay far from the limit to avoid refresh failures, so you can be productive while waiting for your data to refreshes, including and scheduled refreshes in the service.

Solution to prevent throttling: use Function.InvokeAfter to add delay tim4 between API calls.

Sample code example

In Power Query, where there is an ‘each’, such as below:

= Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content]))

 Update the code to add the ‘Function.InvokeAfter’, like this:

= Table.AddColumn(Buffer, "Transform File", each Function.InvokeAfter(()=> #"Transform File"([Content]),#duration(0,0,0,3)))

In this example, 3 seconds elapse between each excel file transform function, and assuming you have 20 or more excel files this works out to be 20 API calls per minute.

Why woud you need to add Function.InvokeAfter() to an ADColumn action?
Isn't the SharePoint data already retrived.. now you are just manupulating the data structure... or am i wrong?

Not manipulating the data structure. Just adding a delay between API calls to stay below the SharePoint 'API Call Speed Limit'. 

v-lid-msft
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

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors