Loop API calls in Power BI

[Anonymous]

Hello!

I am using Power BI to make a call to an API to export a file out. I have to make sure the API call returns "complete" before I can call a second API to successfully export the file.

I have tried using List.Generate to loop a custom function (to get the progress result) but it does not seem that it's looping correctly? 

= List.Generate(
() => [i=0, result = GetResponseExportProgress(ProductID, ProgressID)],
each [i] < 30 and GetResponseExportProgress(ProductID, ProgressID) = "inProgress",
each [i=[i]+1, result = Function.InvokeAfter(()=> fnGetResponseExportProgress(ProductID,ProgressID), #duration(0,0,0,5))]
)

I get a list of records and all records are "inProgress" but when I refresh the table again, the progress turns to "complete".

Here's the code used for GetResponseExportProgress, i have hidden the url and apitoken

(ProductID as text, ProgressID as text) =>
let
GetResponseExportProgress = Json.Document(Web.Contents("[urlhidden]" & ProgressID, [Headers=[#"content-type"="application/json", #"x-api-token"="[apitokenhidden]", Accept="application/json;odata=verbose"]]), null),
GetResponseExportProgressResult = GetResponseExportProgress[result][status]
in GetResponseExportProgressResult

Can anyone help out with this?

[acoyne]

Did you get this resolved? I have similiar issue that I am tried to work through. 

[Anonymous · Accepted solution]

In case anyone needs a solution for this, Power BI caches the API calls that have the exact same parameters. We would need to set IsRetry to true for API to ignore the cached data. Power BI also caches the first call's results that is stored in a variable even if the API is called again afterwards.

[amitchandak]

@Anonymous , refer if this can help

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78