Forum Discussion
Using a Loop to Get Paginated Data in a Non-ODATA Rest API in Power Query
I am a newbie in the API space, and I am trying to pull all the paginated data from UserVoice. They have a limit of 100 results per page, and I can't use the ?limit syntax to increase the limit. I have this so far in Power Query, but it is giving me the same results over and over again. Can anybody assist?
let
url = "https://SUBSITE.uservoice.com/",
Header=[Authorization="TOKEN"],
RelativePathing = "api/v2/admin/suggestions",
Source1 = Json.Document(Web.Contents(url, [RelativePath=RelativePathing, Headers=Header])),
iterations = Source1[pagination][total_pages],
FullURL = url & RelativePathing,
FnGetOnePage =
(FullURL) as record =>
let
Source2 = Json.Document(Web.Contents(url, [RelativePath=RelativePathing, Headers=Header])),
data = try Source2[suggestions] otherwise null,
next = try Source2[pagination][cursor] otherwise null,
res = [Data=data, Next=FullURL & "?cursor=" & next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(FullURL)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])
in
GeneratedList
2 Replies
- parry2kSuper User
- AnonymousNot applicable
HI glove80,
You can also take a look at sample helper functions that shared on official documents:
Helper Functions#tablegeneratebypage
Regards,
Xiaoxin Sheng