Forum Discussion

glove80's avatar
glove80
Microsoft Employee
6 years ago
Solved

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

  • 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!

     

     

2 Replies

  • 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!