Multiple sources from same API without repeating query

[jatpola]

Hello,

I'm bringing a JSON file from an API query (code below) which works fine. Let's call it Main_Source.

let
    body = "SOME PARAMETERS",
    Data = Web.Contents("MY URL",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/x-www-form-urlencoded"]]))
    Source = Json.Document(Data)
in
    Source

From the JSON I'd like to retrieve different tables, so I've created some "children" sources (sample code below) which refer to Main_Source.

let
    Source = Main_Source,
    totals_investment = Source[totals_investment],
    #"Convert to table" = Record.ToTable(totals_investment),
    #"Change type" = Table.TransformColumnTypes(#"Convertido en tabla",{{"Value", type number}})
in
    #"Change type"

Now, my problem is that when I hit "close and apply" the query returns a 429 error (too many requests). I've already:

a) Ran the query outside PowerBI without problems.

b) Refreshed Main_Source's preview, and it downloads the JSON just fine

So I'm assuming (I could be wrong) that the API request is being ran repeatedly when I try to apply changes, causing the 429 error.

Therefore I'd like to include some setting or option so that the API is queried just once, and then the JSON is manipulated without repeating the request.

Hope I've made myself clear, let me know if there's any more info I can supply. Thanks

[parry2k · Accepted solution]

@jatpola maybe turn off parallel processing in the options and then try it.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jatpola]

Thanks, it worked now!

Related to the issue: I was wondering how to perform different API calls to the same URL with different parameters, with some delay between each other, or at least being able to choose which ones to refresh (to avoid clutter). Is that possible somehow?