Hi,
I'm trying to pull in data from an API, which has a workflow as below:
1) Submit a job
2) Wait for job to be processed and data to become available - you can check the status of the job with a call. When the job is completed, the status is marked "completed" and you get a file list.
3) Download the job data.
I'm trying to replicate this within PowerQuery and having trouble with the second step - the status check. My code is as below:
let
//--------------------- Get Access Token -------------------------
accessToken = apiGetAccessToken(basicAuth_user, basicAuth_pw),
//--------------------- Submit a job -----------------------------
jobID = apiJobSubmission(templateName, filterFrom, filterTo),
//--------------------- Check job status and get fileList once completed-------------
jobStatus = (jobID) =>
if Function.Invoke(apiGetJobStatus, {jobID}) = "completed"
then Function.Invoke(apiGetFileList, {jobID})
else
Function.InvokeAfter(()=> @jobStatus(jobID),#duration(0,0,0,30)),
fileList = Function.InvokeAfter(()=> jobStatus(jobID),#duration(0,0,0,1))
in
fileList
Essentially I'm using a recursive function (jobStatus) as a while loop to check the status of the job every 30 seconds. The apiGetJobStatus is a separate function, for which the source code is below. This code works fine and provides me with the result as expected.
(jobID) =>
let
//--------------------- Get Access Token -------------------------
accessToken = apiGetAccessToken(basicAuth_user, basicAuth_pw),
//---------------------- Retrieve Job Status
jobStatusURL = Removed for privacy
//Variables for submitting a job
HTTPHeader = [
Headers = [#"Authorization"="Basic " & Binary.ToText(Text.ToBinary(basicAuth_user&":"&basicAuth_pw)),
#"apiKey"=apiKey,
#"AuthKey"="Bearer " & accessToken,
#"realm"=realm,
#"jobID"=jobID]],
//Send query
response = Web.Contents(jobStatusURL, HTTPHeader),
//Retrieve the status
responseJson = Json.Document(response),
status = responseJson[status]
in
status
My issue is that the recursion function I have never comes out of the loop. What seems to be happening is that once the "apiGetJobStatus" function runs once, it provides a status and then never refreshes, essentially meaning that the loop always thinks the status of the job is pending. I've tried to force the function to run using the "Function.Invoke" method, but to no avail.
I would appreciate any help/insight into why the apiGetJobStatus doesn't seem to refresh. Alternatively, if there's a better method to go through this while loop, I would be open to any suggestions.
Thanks
