Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Powerquery looping over paginated API - dynamic data source error

I am trying to retrieve data from a paginated API - specifically the Zendesk one. The code refreshes on my desktop, but when i try to refresh online i get the 'This dataset includes a dynamic data so...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Found a solution by putitng the start time parameter in the 'query' part of the webcontents function.

  • Anonymous's avatar
    Anonymous
    3 years ago

    sorry I just saw your message. Are you working with the zendesk API? i did manage to make it work in the end. you can play around with the data count number/unix start time.

     

    let
        
        BaseUrl = "https://xxx.zendesk.com",
        Token = "xxx",
        StartingOptions = [RelativePath="/api/v2/incremental/tickets.json?include=comment_count",Headers=[ #"Authorization" = "Basic " & Token ],Query=[start_time="1633089331"]],
         initReq = Json.Document(Web.Contents("https://xxx.zendesk.com", StartingOptions)),
        initData = initReq[tickets],
        gather = (tickets as list, QParm) =>
            let
             BaseUrl = "https://xxx.zendesk.com",
             Token = "xxx",
             StartingOptions = [RelativePath="/api/v2/incremental/tickets.json?include=comment_count",Headers=[ #"Authorization" = "Basic " & Token ],Query=[start_time=QParm]],
             newOffset = Text.From(Json.Document(Web.Contents("https://xxx.zendesk.com",StartingOptions))[end_time]),
             newOptions = [RelativePath="/api/v2/incremental/tickets.json?include=comment_count",Headers=[ #"Authorization" = "Basic " & Token ],Query=[start_time=newOffset]],
             newReq = Json.Document(Web.Contents("https://xxx.zendesk.com",newOptions)),
             newdata = newReq[tickets],
             data = List.Combine({tickets, newdata}),
             datacount = List.Count(data),
             check = if Text.From(newReq[end_of_stream]) = "true" or datacount >=300000 then data else @gather(data, newOffset)
            in check,
       outputList =  if Text.From(initReq[end_of_stream]) = "true" then initData else gather(initData,"1633089331"),
        expand = Table.FromRecords(outputList),
        #"Filtered Rows" = Table.SelectRows(expand, each ([status] <> "deleted"))
       
    in
    #"Filtered Rows"