Forum Discussion
Anonymous
4 years agoNot applicable
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...
- Anonymous4 years ago
Found a solution by putitng the start time parameter in the 'query' part of the webcontents function.
- Anonymous3 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"
Anonymous
4 years agoNot applicable
Found a solution by putitng the start time parameter in the 'query' part of the webcontents function.
- edt3 years agoNew Member
Anonymous - I have a similar issue, are you able to explain a bit more as to what you mean and possibly provide and example please?
- Anonymous3 years agoNot applicable
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"