Using a Loop to Get Paginated Data in a Non-ODATA Rest API in Power Query

[glove80]

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

[v-shex-msft]

HI @glove80,

You can also take a look at sample helper functions that shared on official documents:

Helper Functions#tablegeneratebypage 

Regards,

Xiaoxin Sheng

[parry2k · Accepted solution]

@glove80 hope this post helps.

I would 💖Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!