Using Function.InvokeAfter() In Power Query

Last year I blogged about how to introduce a delay between web service requests in M code. Since then a new function has been added to Power Query which makes this a lot easier: Function.InvokeAfter(). This function doesn’t seem to be documented anywhere apart from the Power Query language reference (downloadable from here); the signature is as follows:

Function.InvokeAfter(function as function, delay as duration) as any

It invokes a function after waiting for a given amount of time. Here’s a simple example of how it can be used that declares a function which returns the current date/time as text, then calls it twice with a five second delay in between:

let
    GetTimeAsText = ()=> DateTime.ToText(DateTime.LocalNow()),
    Output = GetTimeAsText() & " " 
                   & Function.InvokeAfter(GetTimeAsText, #duration(0,0,0,5))
in
    Output

 

The output of this query (at the time of writing) is:

28/04/2015 23:06:38 28/04/2015 23:06:43

One thing that did confuse me a bit was the fact that Function.InvokeAfter() doesn’t allow you to pass a list of arguments for the function you’re invoking like Function.Invoke(). The nice people at Microsoft helped me out with this though, and here’s a slightly more complicated example showing how to use Function.InvokeAfter() with a function that appends “Hello “ to a person’s name:

let
    SayHello = (personname as text) as text => "Hello " & personname,
    Output = Function.InvokeAfter(()=>SayHello("Chris"), #duration(0,0,0,5))
in
    Output

23 responses

  1. Pingback: Waiting Between Web Service Requests In Power Query | Chris Webb's BI Blog

    • Hi Chris, can you give a simple example of how to use this in an query. I’m still hitting the request limit so I’m doing something wrong.

  2. Pingback: Dew Drop – April 30, 2015 (#2004) | Morning Dew

  3. Pingback: Dew Drop – May 1, 2015 (#2005) | Morning Dew

  4. Pingback: Query SAP Business ByDesign Web Services via Power Query – Ivan Bond's blog

  5. Pingback: Timing M Query Execution In Power Query And Power BI (Revisited) – Chris Webb's BI Blog

  6. Pingback: Timing M Query Execution In Power Query And Power BI (Revisited) - SQL Server - SQL Server - Toad World

  7. Pingback: Power BI Diagnostics, Trace Logs And Query Execution Times (Again) – Chris Webb's BI Blog

  8. Hi Chris,

    Can you please provide an sample code on how to use Function.InvokeAfter with Web.Contents. i basically need to query a dynamic web page but wants to wait until all the scripts are done executing before Power Query looks at the DOM. Thanks much

  9. Pingback: How to track refresh time in Power BI Desktop – Ivan Bond's blog

  10. Chris can you help me with insert InvokAfter function in below function?

    let
    AddressLookup = (lat as text, long as text) =>
    let
    Source = Json.Document(Web.Contents(“http://nominatim.openstreetmap.org/reverse?format=jsonv2&lat=”&lat&”&lon=”&long&””)),
    address = Source[address],
    #”Converted to Table” = Record.ToTable(address),
    #”Filtered Rows” = Table.SelectRows(#”Converted to Table”, each ([Name] = “city” or [Name] = “country” or [Name] = “house_number” or [Name] = “postcode” or [Name] = “road”)),
    #”Appended Query” = Table.Combine({#”Filtered Rows”, DefaultValues}),
    #”Removed Duplicates” = Table.Distinct(#”Appended Query”, {“Name”}),
    #”Transposed Table” = Table.Transpose(#”Removed Duplicates”),
    #”Promoted Headers” = Table.PromoteHeaders(#”Transposed Table”, [PromoteAllScalars=true]),
    #”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“house_number”, Int64.Type}, {“road”, type text}, {“city”, type text}, {“postcode”, type text}, {“country”, type text}})
    in
    #”Changed Type”
    in
    AddressLookup

    • Just replace the Source and address steps with the following three lines:

      Source = ()=>Json.Document(Web.Contents(“http://nominatim.openstreetmap.org/reverse?format=jsonv2&lat=”&lat&”&lon=”&long&””)),
      Delay = Function.InvokeAfter(Source, #duration(0,0,0,5)),
      address = Delay[address],

  11. Pingback: Using the Power Function.InvokeAfter to determine how long to wait between API calls – Power BI Business Analytics Solutions

  12. Pingback: Some OData Tips - PowerPivotPro

  13. Hi Chris, I can’t find a way to make this time lag work for the following function. The error I receive is “We cannot convert a value of Type Table to type Function” (fair enough, but I don’t know how to fix it).

    Code for the GetData query:
    let GetResults=(URL) =>
    let
    Source = Excel.Workbook(Web.Contents(URL), null, true),
    #”Exclusion Request_Sheet” = Source{[Item=”Exclusion Request”,Kind=”Sheet”]}[Data],
    #”Changed Type” = Table.TransformColumnTypes(#”Exclusion Request_Sheet”,{{“Column1”, type any}, {“Column2”, type text}, {“Column3”, type any}, {“Column4”, type any}, {“Column5”, type text}, {“Column6”, type any}, {“Column7”, type text}, {“Column8”, type any}, {“Column9”, type any}, {“Column10”, type any}, {“Column11”, type any}, {“Column12”, type text}, {“Column13”, type any}, {“Column14”, type any}, {“Column15”, type any}, {“Column16”, type any}, {“Column17”, type any}, {“Column18”, type any}, {“Column19”, type any}, {“Column20″, type any}})
    in
    #”Changed Type”
    in GetResults

    What this does is take web URLs for several Excel files, downloads and parses them into one matrix. The website has sort of limiter and prevents me from doing more than 20 queries in a row.

    I also tried the function when calling it, e.g. “Function.InvokeAfter(GetData([example.com/sample.xlsx]), #duration(0,0,0,5))” but this doesn’t work either.

    How do I introduce the InvokeAfter function?

    • The first parameter of Function.InvokeAfter needs a value of type function, but when you say
      GetData([example.com/sample.xlsx])
      You are calling a function and returning a table. Try something like:
      Function.InvokeAfter(()=>GetData([example.com/sample.xlsx]), #duration(0,0,0,5))

Leave a Reply to Chris Webb Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

%d bloggers like this: