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
OutputThe 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
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.
It might be easier for you to share the code you are using, so I can see whether you are doing something wrong.
functions passed as parameters. Love it. M really is a pure functional language.
You can use this to (for instance) have custom retry periods based on the HTTP status code of the result. Example: https://gist.github.com/CurtHagenlocher/68ac18caa0a17667c805
That looks very useful, thanks!
For the use case of sequentially fetching a list of URLs, here’s an example for that:
https://gist.github.com/tycho01/5db0e8974036d7692c9e
🙂
Thanks!
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
If you have a working query, all you need to do is take the step that calls Web.Contents() and convert it to a function. There’s another example of how to use Function.InvokeAfter() here if it helps: https://blog.crossjoin.co.uk/2016/04/04/timing-m-query-execution-in-power-query-and-power-bi-revisited/
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],
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))
Hi Chris, please
I need duration (0,0,0,5)
for this
#”Функция после сборки:
let GetResults=(URL) =>
let
Источник = 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″})
in
#”Удаленные столбцы1″
in GetResults
and:
let
Источник = 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}})
in
#”Измененный тип1”
thanks
Hello Chris,
I have the same problem. I can’t add function.invokeafter. Can you help me please?
let
Source = Json.Document(Web.Contents(“https://api.io/v1/catalog?X-API-KEY=” & “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(“https://api.io/v1/catalog?X-API-KEY=” & “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.id”, “items.sku”, “items.status”, “items.highlight”, “items.new”, “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”}, {“items.i18n.pt”}),
#”Expanded items.i18n.pt” = Table.ExpandRecordColumn(#”Expanded items.i18n”, “items.i18n.pt”, {“title”, “description”, “small_description”, “specs”, “seo_url”, “seo_title”, “seo_description”, “seo_metatag”, “digital_file”, “barcode”}, {“items.i18n.pt.title”, “items.i18n.pt.description”, “items.i18n.pt.small_description”, “items.i18n.pt.specs”, “items.i18n.pt.seo_url”, “items.i18n.pt.seo_title”, “items.i18n.pt.seo_description”, “items.i18n.pt.seo_metatag”, “items.i18n.pt.digital_file”, “items.i18n.pt.barcode”}),
#”Expanded items.images” = Table.ExpandListColumn(#”Expanded items.i18n.pt”, “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”}, {“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”}, {“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”}, {“items.extra_fields.i18n.pt”}),
#”Expanded items.extra_fields.i18n.pt” = Table.ExpandRecordColumn(#”Expanded items.extra_fields.i18n”, “items.extra_fields.i18n.pt”, {“field”, “value”}, {“items.extra_fields.i18n.pt.field”, “items.extra_fields.i18n.pt.value”}),
#”Expanded items.specs” = Table.ExpandListColumn(#”Expanded items.extra_fields.i18n.pt”, “items.specs”),
#”Expanded items.variations” = Table.ExpandListColumn(#”Expanded items.specs”, “items.variations”)
in
#”Expanded items.variations”
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
let
source = OData.Feed(“https://api.security.microsoft.com/api/” & Query, null, [Implementation=”2.0″, MoreColumns=true]),
#”toList” = {source},
#”toRecord” = Record.FromList(#”toList”, {“Record”})
in
#”toRecord”
else
let
source = OData.Feed(“https://api.security.microsoft.com/api/” & Query, null, [Implementation=”2.0″, MoreColumns=true]),
#”toTable” = #table(1, {{source}})
in
#”toTable”
where is the error in this one ?
(CNPJ as text) =>
let
Fonte = Function.InvokeAfter(()=>Json.Document(Web.Contents(“https://receitaws.com.br/v1/cnpj/&CNPJ”))),#duration(0,0,0,20))
in
Fonte
It looks like you have an extra bracket. Try:
let
Fonte = Function.InvokeAfter(()=>Json.Document(Web.Contents(“https://receitaws.com.br/v1/cnpj/&CNPJ”)),#duration(0,0,0,20))
in
Fonte
Thanks! This article solved one of my problems with hitting the API too frequently from PowerQuery.