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"
edt
3 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?
Anonymous
3 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"