API Timed Call Limit Reached in Power Query

[VinceA]

I am working on a portfolio project where I am trying to analyze weekly stock data over the past two years. I have connected Power BI to a free AlphaVenture API but am running into an issue when trying to run my API function as I am hitting the call limit (5 calls per minute).

Does anyone know how to get around this issue? Would really appreciate it!

Copy of my M code: https://ufile.io/gtqr80jl

[mahoneypat]

Please see this article to see how to add a delay after each call.

Chris Webb's BI Blog: Using Function.InvokeAfter() In Power Query Chris Webb's BI Blog (crossjoin.co...

Pat

[VinceA]

Hello,

Thank you for the quick response and the reference. I've tried to use function.invokeafter for the past couple of hours in different orders in the query but I can't seem to get it to work. I do not have much experience with M - all of my work in PowerQuery has been using the UI.

Below is a copy of my most recent attempt. I seem to be adding a delay since invoking the custom function is taking minutes as opposed to seconds, but I am still getting an error, which I'm assuming means I am delaying something other than the actual call to the API.

let
    Output = Function.InvokeAfter(()=> Source, #duration(0,0,0,13)),
    Source = (symbol) =>
    
    let
        Source = Json.Document(Web.Contents(
            "https://www.alphavantage.co/",
            [
            RelativePath="query",
            Query=
                [
                function = "TIME_SERIES_WEEKLY",
                symbol=symbol,
                apikey="placeholder"
                ]
            ]
    )),

        #"Weekly Time Series" = Source[Weekly Time Series],
        #"Converted to Table" = Record.ToTable(#"Weekly Time Series"),
        #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"1. open", "2. high", "3. low", "4. close", "5. volume"}, {"1. open", "2. high", "3. low", "4. close", "5. volume"})
    in
        #"Expanded Value"
in
    Output

Depending on the order I add function.invokeafter in, I get errors about converting a function to a record. Would you be able to provide specific input on where/how to add the function? It would be very much appreciated since I am unfortunately very stuck.