Waiting Between Web Service Requests In Power Query

Often when you’re working with web services in Power Query you’ll be making a large number of requests to the web service in a small amount of time – and you’ll notice that your query seems to be a lot slower than you would expect. The reason is, of course, that many public websites and APIs implement throttling to prevent you from making too many requests all at once, I guess to prevent denial-of-service attacks.

While looking into this issue I found a very interesting post containing the solution to this problem on the Power Query forum, from Alejandro Lopez-Lago. In it he shows how to create a function that creates a delay of a specified number of seconds in a Power Query query. Here’s an example of how to use it in a query:

    //Find current time
    One = DateTime.LocalNow(),
    //Define Wait function
    Wait = (seconds as number, action as function) => 
            if (List.Count(
              () => DateTimeZone.LocalNow() + #duration(0,0,0,seconds), 
              (x) => DateTimeZone.LocalNow() < x, 
              (x) => x)
              ) = 0) 
               then null else action(),
    //Call Wait function to wait 5 seconds
    //then return the current time again
    Two = Wait(5,DateTime.LocalNow),
    //Display start time, finish time, and duration
    Output = "Start Time: " & DateTime.ToText(One) & 
                   " Finish Time:" & DateTime.ToText(Two) & 
                   " Difference: " & Duration.ToText(Two-One)


Here’s an example of the output:


The important step in this query is Wait, which defines the function. It works by using List.Generate() to keep on generating a list until a given number of seconds (passed in though the parameter seconds) have elapsed; when that has happened, it will execute the function that is passed in through the parameter action. Very clever stuff!

UPDATE: There’s now a better way of doing all of this, using the Function.InvokeAfter() function https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/

6 responses

  1. Pingback: Warteschleife in Power Query realisieren | MS Excel | Power Pivot | DAX

  2. Pingback: Timing Power Query Queries | Chris Webb's BI Blog

  3. Pingback: Timing Power Query Queries - SQL Server - SQL Server - Toad World

  4. Pingback: Using Function.InvokeAfter() In Power Query | Chris Webb's BI Blog

  5. Pingback: Using Function.InvokeAfter() In Power Query - SQL Server - SQL Server - Toad World

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: