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:

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


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:

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

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

    AddressLookup = (lat as text, long as text) =>
    Source = Json.Document(Web.Contents(“”&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}})
    #”Changed Type”

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

      Source = ()=>Json.Document(Web.Contents(“”&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) =>
    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}})
    #”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([]), #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
      You are calling a function and returning a table. Try something like:
      Function.InvokeAfter(()=>GetData([]), #duration(0,0,0,5))

  14. Hi Chris, please
    I need duration (0,0,0,5)
    for this

    #”Функция после сборки:

    let GetResults=(URL) =>
    Источник = Web.Page(Web.Contents(URL)),
    Data0 = Источник{0}[Data],
    #”Пониженные заголовки” = Table.DemoteHeaders(Data0),
    #”Измененный тип” = Table.TransformColumnTypes(#”Пониженные заголовки”,{{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type any}, {“Column4″, type text}}),
    Column3 = #”Измененный тип”{1}[Column3],
    #”Развернутый элемент Children” = Table.ExpandTableColumn(Column3, “Children”, {“Kind”, “Name”, “Children”, “Text”}, {“Kind.1”, “Name.1”, “Children.1”, “Text.1″}),
    #”Развернутый элемент Children.1″ = Table.ExpandTableColumn(#”Развернутый элемент Children”, “Children.1”, {“Kind”, “Name”, “Children”, “Text”}, {“Kind.2”, “Name.2”, “Children”, “Text.2″}),
    #”Развернутый элемент Children1″ = Table.ExpandTableColumn(#”Развернутый элемент Children.1”, “Children”, {“Kind”, “Name”, “Children”, “Text”}, {“Kind.3”, “Name.3”, “Children.1”, “Text.3″}),
    #”Развернутый элемент Children.2″ = Table.ExpandTableColumn(#”Развернутый элемент Children1”, “Children.1”, {“Kind”, “Name”, “Children”, “Text”}, {“Kind.4”, “Name.4”, “Children”, “Text.4″}),
    #”Развернутый элемент Children2″ = Table.ExpandTableColumn(#”Развернутый элемент Children.2”, “Children”, {“Kind”, “Name”, “Children”, “Text”}, {“Kind.5”, “Name.5”, “Children.1”, “Text.5″}),
    #”Строки с примененным фильтром” = Table.SelectRows(#”Развернутый элемент Children2″, each ([Text.5] null and [Text.5] “#(lf)” and [Text.5] “#(lf) ” and [Text.5] “#(lf) “)),
    #”Удаленные столбцы” = Table.RemoveColumns(#”Строки с примененным фильтром”,{“Text.4”, “Text.3”, “Text.2”, “Text.1”, “Text”, “Kind”, “Name”, “Kind.1”, “Name.1”, “Kind.2”, “Name.2”, “Kind.3”, “Name.3”, “Kind.4”, “Name.4”, “Kind.5”, “Name.5”, “Children.1″}),
    #”Транспонированная таблица” = Table.Transpose(#”Удаленные столбцы”),
    #”Удаленные столбцы1″ = Table.RemoveColumns(#”Транспонированная таблица”,{“Column1”, “Column4”, “Column5”, “Column6”, “Column7”, “Column8”, “Column9”, “Column10″})
    #”Удаленные столбцы1″
    in GetResults


    Источник = Excel.CurrentWorkbook(){[Name=”Ссылки”]}[Content],
    #”Измененный тип” = Table.TransformColumnTypes(Источник,{{“Об’єкт”, type text}, {“Текст ссилки”, type text}, {“URL”, type text}}),
    #”Добавлен пользовательский объект” = Table.AddColumn(#”Измененный тип”, “Пользовательская”, each #”Функция после сборки”([URL])),
    #”Развернутый элемент Пользовательская” = Table.ExpandTableColumn(#”Добавлен пользовательский объект”, “Пользовательская”, {“Column2”, “Column3”}, {“Column2”, “Column3″}),
    #”Измененный тип1″ = Table.TransformColumnTypes(#”Развернутый элемент Пользовательская”,{{“Column2”, type text}, {“Column3″, type text}})
    #”Измененный тип1”


  15. Hello Chris,

    I have the same problem. I can’t add function.invokeafter. Can you help me please?

    Source = Json.Document(Web.Contents(“” & “API-KEY” & “&limit=” & “25”)),
    pages = {1..Source[paging][totalpages]},
    #”Converted to Table” = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Changed Type” = Table.TransformColumnTypes(#”Converted to Table”,{{“Column1″, type text}}),
    #”Added Custom” = Table.AddColumn(#”Changed Type”, “Data”, each Json.Document(Web.Contents(“” & “API-KEY” & “&limit=” & “25” & “&page=” & [Column1]))),
    #”Expanded Data” = Table.ExpandRecordColumn(#”Added Custom”, “Data”, {“items”}, {“items”}),
    #”Expanded items” = Table.ExpandListColumn(#”Expanded Data”, “items”),
    #”Expanded items1″ = Table.ExpandRecordColumn(#”Expanded items”, “items”, {“id”, “sku”, “status”, “highlight”, “new”, “stock”, “allow_extra_stock”, “showprice”, “weight”, “type”, “mode”, “views”, “permissions”, “id_brand”, “allow_cart_upload”, “unit_type”, “default_tax”, “default_imagem”, “thumb”, “prices”, “i18n”, “images”, “attachments”, “related_ids”, “extra_fields”, “specs”, “variations”}, {“”, “items.sku”, “items.status”, “items.highlight”, “”, “items.stock”, “items.allow_extra_stock”, “items.showprice”, “items.weight”, “items.type”, “items.mode”, “items.views”, “items.permissions”, “items.id_brand”, “items.allow_cart_upload”, “items.unit_type”, “items.default_tax”, “items.default_imagem”, “items.thumb”, “items.prices”, “items.i18n”, “items.images”, “items.attachments”, “items.related_ids”, “items.extra_fields”, “items.specs”, “items.variations”}),
    #”Expanded items.prices” = Table.ExpandListColumn(#”Expanded items1″, “items.prices”),
    #”Expanded items.prices1″ = Table.ExpandRecordColumn(#”Expanded items.prices”, “items.prices”, {“price_table”, “tax_included”, “master”, “allow_countries”, “type”, “values”}, {“items.prices.price_table”, “items.prices.tax_included”, “items.prices.master”, “items.prices.allow_countries”, “items.prices.type”, “items.prices.values”}),
    #”Expanded items.prices.values” = Table.ExpandListColumn(#”Expanded items.prices1″, “items.prices.values”),
    #”Expanded items.prices.values1″ = Table.ExpandRecordColumn(#”Expanded items.prices.values”, “items.prices.values”, {“normal_price”, “discount_price”, “start_discount_price”, “end_discount_price”}, {“items.prices.values.normal_price”, “items.prices.values.discount_price”, “items.prices.values.start_discount_price”, “items.prices.values.end_discount_price”}),
    #”Expanded items.i18n” = Table.ExpandRecordColumn(#”Expanded items.prices.values1″, “items.i18n”, {“pt”}, {“”}),
    #”Expanded” = Table.ExpandRecordColumn(#”Expanded items.i18n”, “”, {“title”, “description”, “small_description”, “specs”, “seo_url”, “seo_title”, “seo_description”, “seo_metatag”, “digital_file”, “barcode”}, {“”, “”, “”, “”, “”, “”, “”, “”, “”, “”}),
    #”Expanded items.images” = Table.ExpandListColumn(#”Expanded”, “items.images”),
    #”Expanded items.images1″ = Table.ExpandRecordColumn(#”Expanded items.images”, “items.images”, {“image”, “thumb”, “order”, “width”, “height”, “i18n”}, {“items.images.image”, “items.images.thumb”, “items.images.order”, “items.images.width”, “items.images.height”, “items.images.i18n”}),
    #”Expanded items.images.i18n” = Table.ExpandRecordColumn(#”Expanded items.images1″, “items.images.i18n”, {“pt”}, {“”}),
    #”Expanded items.attachments” = Table.ExpandListColumn(#”Expanded items.images.i18n”, “items.attachments”),
    #”Expanded items.attachments1″ = Table.ExpandRecordColumn(#”Expanded items.attachments”, “items.attachments”, {“file”, “order”, “i18n”}, {“items.attachments.file”, “items.attachments.order”, “items.attachments.i18n”}),
    #”Expanded items.attachments.i18n” = Table.ExpandRecordColumn(#”Expanded items.attachments1″, “items.attachments.i18n”, {“pt”}, {“”}),
    #”Expanded items.related_ids” = Table.ExpandListColumn(#”Expanded items.attachments.i18n”, “items.related_ids”),
    #”Expanded items.extra_fields” = Table.ExpandListColumn(#”Expanded items.related_ids”, “items.extra_fields”),
    #”Expanded items.extra_fields1″ = Table.ExpandRecordColumn(#”Expanded items.extra_fields”, “items.extra_fields”, {“id_extra_field”, “i18n”}, {“items.extra_fields.id_extra_field”, “items.extra_fields.i18n”}),
    #”Expanded items.extra_fields.i18n” = Table.ExpandRecordColumn(#”Expanded items.extra_fields1″, “items.extra_fields.i18n”, {“pt”}, {“”}),
    #”Expanded” = Table.ExpandRecordColumn(#”Expanded items.extra_fields.i18n”, “”, {“field”, “value”}, {“”, “”}),
    #”Expanded items.specs” = Table.ExpandListColumn(#”Expanded”, “items.specs”),
    #”Expanded items.variations” = Table.ExpandListColumn(#”Expanded items.specs”, “items.variations”)
    #”Expanded items.variations”

  16. Pingback: Chris Webb's BI Blog: Delaying Power BI Dataset Refresh Until The Source Data Is Ready Chris Webb's BI Blog

  17. hi Chris,
    It seems not to be working for me. Can you help me add it to the code below (Query as text, ResultType as text) => if ResultType = “record” then
    source = OData.Feed(“” & Query, null, [Implementation=”2.0″, MoreColumns=true]),
    #”toList” = {source},
    #”toRecord” = Record.FromList(#”toList”, {“Record”})
    source = OData.Feed(“” & Query, null, [Implementation=”2.0″, MoreColumns=true]),
    #”toTable” = #table(1, {{source}})

  18. where is the error in this one ?

    (CNPJ as text) =>

    Fonte = Function.InvokeAfter(()=>Json.Document(Web.Contents(“”))),#duration(0,0,0,20))

    • It looks like you have an extra bracket. Try:

      Fonte = Function.InvokeAfter(()=>Json.Document(Web.Contents(“”)),#duration(0,0,0,20))

  19. Pingback: Optimising OData Refresh Efficiency in Energy Question for Energy BI and Excel - HitWeb

  20. Pingback: Optimising OData Refresh Performance in Power Query for Power BI and Excel – LearnMorExcel

  21. Pingback: Optimising OData Refresh Efficiency in Energy Question for Energy BI and Excel -

Leave a Reply to Joe Cancel reply

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

%d bloggers like this: