topic Re: Retrieving data from SharePoint causing throttling errors in Service

Retrieving data from SharePoint causing throttling errors

hansei — Mon, 10 Feb 2020 01:43:00 GMT

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?

Re: Retrieving data from SharePoint causing throttling errors

v-lid-msft — Mon, 10 Feb 2020 08:57:55 GMT

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,

Re: Retrieving data from SharePoint causing throttling errors

hansei — Mon, 10 Feb 2020 15:29:46 GMT

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

Re: Retrieving data from SharePoint causing throttling errors

hansei — Mon, 10 Feb 2020 17:43:06 GMT

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.

Re: Retrieving data from SharePoint causing throttling errors

hansei — Mon, 17 Feb 2020 21:08:20 GMT

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.

Re: Retrieving data from SharePoint causing throttling errors

v-lid-msft — Wed, 19 Feb 2020 05:20:01 GMT

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,

Re: Retrieving data from SharePoint causing throttling errors

hansei — Wed, 19 Feb 2020 06:18:57 GMT

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.

Re: Retrieving data from SharePoint causing throttling errors

v-lid-msft — Wed, 19 Feb 2020 06:41:13 GMT

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,

Re: Retrieving data from SharePoint causing throttling errors

hansei — Wed, 19 Feb 2020 06:44:58 GMT

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

Re: Retrieving data from SharePoint causing throttling errors

v-lid-msft — Wed, 19 Feb 2020 07:08:06 GMT

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 document, When not specified, API version 14 is used.

Best regards,

Re: Retrieving data from SharePoint causing throttling errors

hansei — Wed, 19 Feb 2020 20:32:38 GMT

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.

Re: Retrieving data from SharePoint causing throttling errors

v-lid-msft — Thu, 20 Feb 2020 07:26:22 GMT

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/

Best regards,

Re: Retrieving data from SharePoint causing throttling errors

Andrew501 — Tue, 10 Mar 2020 16:18:02 GMT

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.

Re: Retrieving data from SharePoint causing throttling errors

hansei — Tue, 10 Mar 2020 19:26:20 GMT

Andrew501, thanks for your insight.

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

Re: Retrieving data from SharePoint causing throttling errors

Anonymous — Fri, 07 Aug 2020 07:21:32 GMT

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

Re: Retrieving data from SharePoint causing throttling errors

hansei — Fri, 14 Aug 2020 22:40:03 GMT

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.

Re: Retrieving data from SharePoint causing throttling errors

Anonymous — Wed, 16 Sep 2020 01:22:04 GMT

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).

Re: Retrieving data from SharePoint causing throttling errors

Andrew_Taylor — Wed, 21 Jul 2021 14:54:52 GMT

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:

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.

Re: Retrieving data from SharePoint causing throttling errors

jkova — Tue, 20 Dec 2022 17:41:17 GMT

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?

Re: Retrieving data from SharePoint causing throttling errors

Andrew_Taylor — Wed, 21 Dec 2022 15:27:29 GMT

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