Re: Power Query Recursive Function returning error...

ErisedAlurem · ‎08-29-2022

Hello!

I'm trying to retrieve data from an API, and I'm having a hard time with what seems to be a simple task:

The API returns the information in the following format:

A record with two informations: hasMore, wich defines if there are still items left to retrieve and items, a list with what I wanted to retrieve. Since I need all data (until hasMore = FALSE), my goal is to get a List with all items (not the list limited to the max defined by the API).

(The list gives me 100 records)

With this goal in mind, I've tried the following:

Ticket_Query = (optional _after)=> 
let
  //definir quantos tickets pular
    _skip = ",startingAfter=" & _after,

  //obter fonte de dados
   Source = Json.Document(Web.Contents(urlgerada & _skip,
    [Headers=[token="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"]])),
    StepInter = Text.From(Record.Field(Source,"hasMore")),
    teste2 = Record.Field(Source,"items"),
    teste3 = Record.Field(Source,"items"){99}[id],
    Nextstep = 
      if Text.From(Record.Field(Source,"hasMore")) = "true" and List.Count(teste2)<=198 then 
      teste2 & Record.Field(@Ticket_Query(Record.Field(Source,"items"){99}[id]),"items")
     else teste2
in
    Nextstep
    in  
   Ticket_Query("")

I created a function (Ticket_query) with an optional parameter, that retrieves the id from the last record in the previous consoult and uses it as the API's parameter "startingAfter".

I retrieve this information with the following query:

teste3 = Record.Field(Source,"items"){99}[id], wich appears to work properly:

(wich is correct)

Then I create the query, with the base URL and the skip _parameter.

I then created the variable teste2, that retrieves only the list of items (ignoring "hasMore").

Finally I use the following logic: if the API returns "hasMore" = TRUE, the query should return to me teste2 appended to a list similar to teste2, but now using Ticket_query with the parameter recieved from the first consoult (the recursion is using the id from the last item - equivalent to teste3, as showed before.

Record.Field(Source,"items"){99}[id]

).

When I try to get only the first page ( List.Count(teste2)<=99 ) the query works, but when I try to retrieve more data ( List.Count(teste2)<=198 or bigger) the query crashes. I imagine that I'm doing something wrong in the recursion, but I can't find what.

Any input would be appretiated.

Unfortunaly the API is private, so I can't show all of the code (nor give access to the token)