Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cursor Paginated data from Web API

Hi,   I am calling a web api to grab some data which is paginated and I am struggling with how to get the next page of data as it is cursor based.   When I call the api in PowerQuery (I'm connect...
Need Help
ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi Anonymous ,

sorry, but I'm not able to follow without seeing anyting that's returned.

Could you please paste some screenshots or create some mockup data that's illustrating the problem?

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ImkeF ,

 

Yep, absolutely no problem.  I'll try to lay out where I am with this at the moment with some images, data and code.   Apologies if this goes over some things we've already spoken about.

 

So far I have created a simple function to accept a URL that calls a web service and returns an XML data set:

 

(url as text) as table =>
let
    Source = Xml.Tables(Web.Contents(url))
in
    Source

 

If I pass the following url:

 

https://api.myWebService.com/v1/channel/12345/webcasts

 

I get the following:

 

Expanding the Table column gives me this:

 

As you can see, the last row in the data set contains values in the Table.Attribute:href and Table.Attribute:rel columns that indicate that there is another page of data and the URL with a cursor parameter appended to it.

 

I've tried to write a query that invokes the function and uses the List.Generate function to paginate through the data, passing the value in Table.Attribute:href to the function, building a list of Tables until there are no further pages to process.

 

Using the help that you've given in other threads and in this one also, I've written this query but as you can see, for some reason it doesn't seemt to like the field reference:

 

If I remove the [Attribute:href] the code shows no errors and returns the inital dataset but goes no further (as you can see the loop is set to return 5 pages for testing purposes):

 

 

I hope this helps, but if you need any further information please let me know!

Many Thanks

 

 

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    Hi Anonymous ,

    try to delete the XML.Tables(Web.Contents( -part of the query. 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ImkeF,

       

      No luck I'm afraid.  This is the code now but I still get an Invalid Identifier error for the Attribute part.

       

      let
myList = List.Generate( ()=>
    [Result = try Query4("https://api.brighttalk.com/v1/channel/10737/webcasts") otherwise null, Counter = 0],
    each [Result] <> null and [Counter] < 5,
    each [Result = try Query4(Table.LastN([Result], 1)[Attribute:href]) otherwise null, Counter = [Counter]+1],
    each [Result])
in
    myList

       

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi Anonymous ,

        for debugging purposes, please use this query:

         

        let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

         

        It should create a list and there you have to navigate to the errors and paste screenshots of them please.