CODEWITHSUNDEEP
powerquerym
Akos Nagy
Akos Nagy

Reputation: 4350

Power Query delayed recursion

I'm very new to Power Query and trying to piece a little demo together in Excel.

I have two web endpoints: I have to post some content to the first endpoint, this gives me the url of the second endpoint and then I have to query this second endpoint for the actual results. The second endpoint gives back a json response and in it, there is a field that represents if the results are ready or not. If the results are ready, they can be processed, if not, the endpoint should be queried again at a later date.

Here's the code I have so far:

let   
    apikey      = "MYAPIKEY",        
    proxyendpoint = "URL OF THE FIRST ENDPOINT",
    bytesbody   = File.Contents("FILE TO POST"),
    headers     = [#"Ocp-Apim-Subscription-Key" = apikey],
    bytesresp   = Web.Contents(proxyendpoint, [Headers=headers, Content=bytesbody]),    
    jsonresp    = Json.Document(bytesresp),    
    opLoc       = jsonresp[OperationLocation],
    getResult = (url) =>
        let           
           linesBody = Web.Contents(url, [Headers=headers]),
           linesJson = Json.Document(linesBody),
           resultStatus = linesJson[status],
           linesData = if (resultStatus = "Succeeded") then
                              linesJson[recognitionResult][lines]
                       else 
                              Function.InvokeAfter(()=>@getResult(url),#duration(0,0,0,5))
       in
          linesData,
   linesText = List.Transform(getResult(opLoc), each _[text]),   
   table = Table.FromList(linesText)
in
   table

My problem is that when I check with Fiddler, I see the second endpoint queried once, I can check there in the response that the results are not ready, the data loading "hangs", but I cannot see any additional calls to the second endpoint, so basically my recursive calls are not being evaluated.

What am I doing wrong?

Upvotes: 2

Views: 450

Answers (2)

Akos Nagy
Akos Nagy

Reputation: 4350

Turns out my code was basically right. The issue was that Web.Contents() does some internal caching, that's why I couldn't see any more calls in Fiddler and that's why my data loading "hang" (since the first time the recursion exit criterion was false and the result got cached, every subsequent recursion just used the same data).

I created some POCs for the delayed recursion scenario and strangely, everything worked. I changed things around until I reached a version of the POC where the only difference was the Web.Contents() call. So I did a search for this specific issue and found a post here.

So as suggested in this post, I added a new header value to every Web.Contents() call to avoid the response being cached (also cleaned up the code a bit):

let   
    apikey      = "MYAPIKEY",   
    proxyendpoint = "URL OF THE FIRST ENDPOINT",
    bytesbody   = File.Contents("FILE PATH TO BE POSTED"),
    headers     = [#"Ocp-Apim-Subscription-Key" = apikey],
    bytesresp   = Web.Contents(proxyendpoint, [Headers=headers, Content=bytesbody]),    
    jsonresp    = Json.Document(bytesresp),    
    opLoc = jsonresp[OperationLocation],  
    getResult = (url, apiKeyParam) =>
      let
        // note the extra header here, which is different in every call
        currentHeaders = [#"Ocp-Apim-Subscription-Key" = apiKeyParam, #"CacheHack" = Number.ToText(Number.Random())],
        linesBody = Web.Contents(url, [Headers=currentHeaders]),
        linesJson = Json.Document(linesBody),
        resultStatus = linesJson[status],
        result = if (resultStatus = "Succeeded") then linesJson[recognitionResult][lines]
                 else Function.InvokeAfter(()=>@getResult(url, apiKeyParam), #duration(0,0,0,5))
       in result,
    linesText = List.Transform(getResult(opLoc, apikey), each _[text]),   
    table = Table.FromList(linesText)    
 in table

Upvotes: 0

MarcelBeug
MarcelBeug

Reputation: 2967

With the ()=> in the first argument of Function.InvokeAfter, the result of Function.InvokeAfter will be the function getResult, rather than the result from getResult. So it should be left out:

Function.InvokeAfter(@getResult(url),#duration(0,0,0,5))

Upvotes: 2

Related Questions