Search code examples
excelpowerquerym

Function.InvokeAfter trouble

I'm trying to delay each call to StockData with 5 seconds in order to get by an API call restriction. Can't seem to get it done right, what am I doing wrong?

let
    Source = Excel.CurrentWorkbook(){[Name="Stocks"]}[Content],
    #"Invoked Custom Function" = Function.InvokeAfter(Table.AddColumn(Source, "StockData", each StockData([Full Name])), #duration(0,0,0,5)),
    #"Expanded StockData" = Table.ExpandTableColumn(#"Invoked Custom Function", "StockData", {"Name", "Value.1. open", "Value.2. high", "Value.3. low", "Value.4. close", "Value.5. volume"}, {"StockData.Name", "StockData.Value.1. open", "StockData.Value.2. high", "StockData.Value.3. low", "StockData.Value.4. close", "StockData.Value.5. volume"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded StockData",{"Size", "Short Name", "ISIN"})
in
    #"Removed Columns"

Related code if anyone would like to try it out:

let StockData = (stock as text) =>

        let
            Source = Json.Document(Web.Contents("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & stock & "&outputsize=compact&apikey=RG5AM4ORXWE1X0V8")),
            #"Time Series (Daily)" = Source[#"Time Series (Daily)"],
            #"Converted to Table" = Record.ToTable(#"Time Series (Daily)"),
            #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"1. open", "2. high", "3. low", "4. close", "5. volume"}, {"Value.1. open", "Value.2. high", "Value.3. low", "Value.4. close", "Value.5. volume"})
        in
            #"Expanded Value"
in StockData
Solution

  • You need to apply Function.InvokeAfter to the function (not to the table), and you need to change the syntax slightly to pass arguments to that function.

    Rewrite the second line as:

    #"Invoked Custom Function" = Table.AddColumn(Source, "StockData", each Function.InvokeAfter(()=>StockData([Full Name])), #duration(0,0,0,5))),