Need a Sleep or Wait step in my Query - hitting API rate limits

[Evogelpohl]

I have a working Power query function to pass ~12,000 tweets to MS cognitive sentiment API.  Working just fine.

I'm paying for the S1 (100,000 calls / 30 days) pricing teir.  Even in testing my script, i've not used anywhere near that many.

I'm starting my final analysis now and want it to run.  However, After 50 I get this: 

message=Rate limit is exceeded. Try again in 15 seconds. statusCode: 429.

Perhaps I'm going too fast and the problem isn't that i've breached my limit.

Is there a Power Query command similar to SLEEP 15 to wait or pause for 15 seconds and then do the next bunch?

Ideas?  Thanks.

? @DataChant 

I'm hitting a rate limit from the Cognitive Text Analysis services.

[sglemmestad]

Hi,

Look at this article. This slowed it for me. 

Building delays into Power BI API queries — Function.InvokeAfter() and Google Maps API | by Andre Al...

[GilbertQ]

Hi there

Please find below a blog post by Chris Webb which explains how to very easily do this using the Power Query function called Function.Invoke()

I am sure that this will help you set a time limit.

https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/

[Evogelpohl]

@GilbertQ Thanks.  I found that as well. 

I'm having troubles getting it to work in my case.

I have a query to get 10,000 items from a SharePoint list -> return to a list in PowerQuery.

Next, I wrote a function, a second query called fxGetSentiment, that uses the API for: https://westus.api.cognitive.microsoft.com/text/analytics/v2.0.

I then go back to the original query and choose ADD COLUMN - into which i put:  "try fxGetSentiment(id,tweet)".  It then runs for each of the 10K lines and pulls back the sentiment score from the second query, the Fx.

It works great for 50-70 queries, then I get error:  "Exceeding API rate limit, try back in X seconds".

I'm not sure where to place Chris Webb's code.  Here's my function below.  It would seem like it should go here, but I can't figure out where to put it.

let

    TweetCognitive = (TweetID as text, TweetText as text) =>

let
    JsonRecords = Text.FromBinary(Json.FromValue([id=TweetID, text=TweetText])),
    JsonRequest = "{""documents"": [" & JsonRecords & "]}",
    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"="MYKEYHERE",
                           #"Content-Type"="application/json", Accept="application/json"],
                       Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse

in
    TweetCognitive

[GilbertQ]

@Evogelpohl I think that this might work

let

    TweetCognitive = (TweetID as text, TweetText as text) =>

let
    JsonRecords = Text.FromBinary(Json.FromValue([id=TweetID, text=TweetText])),
    JsonRequest = "{""documents"": [" & JsonRecords & "]}",
    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"="MYKEYHERE",
                           #"Content-Type"="application/json", Accept="application/json"],
                       Content=JsonContent 
            ]),
    Delay = Function.InvokeAfter(Response, #duration(0,0,0,5)),
    JsonResponse = Json.Document(Delay ,1252)
in
    JsonResponse

in
    TweetCognitive

[Evogelpohl]

@GilbertQ

Thanks.  I see what you're doing here.  Though, I cut/paste & reset MYEKY & the calls are happening at the same speed as they did before. 

I have Fiddler up and running and can see each call to the API.  They are going very fast - must faster than every 5 seconds.

It's like the Function.InvokeAfter portion is ignored.  Each red line is the error "Rate limit exceeded, try back in X seconds"

The black lines work as designed.  

[GilbertQ]

I would say possibly to move the delay then before it makes the web call and see if that works?

It might just be to find the right place before it makes the web call, and looking at it now, there is no use to put it after the web call, but before?

[DataChant · Accepted solution]

BTW - You may want to re-evaluate your approach. From the initial code above, you post a single message per API call. Instead you can send 1000 messages in a single API call, and repeat it on your entire dataset. You can learn how to do it in my blog here.

[Evogelpohl]

@DataChant thanks.  At some point in my power query learning - most likely from reading Kurt H's posts - I got fixated on the process of making functions out of queries and then using the add column pattern to add data to an existing source.  I'm going to try to crack this nut, else I'll adopt your pattern.  -Eric.