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

19 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],

Leave a 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 )

w

Connecting to %s

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

%d bloggers like this: