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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
I know that ImkeF has done things like this.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ImkeF 

     

    Many thanks for the help and resources.  I've read through it and I feel like I'm almost there but I just can't quite join the dots. I'm very new to PowerQuery so some of the syntax is still a bit of a mystery to me but I'm getting there!

     

    My data is returned as xml (I don't think that will make much difference) and the last  row of the page has two fields, Attribute:href and Attribute:rel.  The rel field contains the word next and the rel field a complete url with the cursor for the next page. The API documentation states that when the last page is reached there will be no value in the rel field.

     

    As I understand it, I can use the List.Generate function and effectively 'loop' through the data and pass the next url and grab the next page of data.  I can't seem to figure out how to reference the fields from my Source and use it to get the next set of data.  This is what I have so far (which doesn't work!)

     

    let
    Source = Xml.Tables(Web.Contents("https://api.myWebService.com/v1/channel/1234/webcasts")),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"title", "description", "presenter", "duration", "start", "keywords", "published", "visibility", "url", "status", "active", "created", "lastUpdated", "link", "Attribute:id", "syndicationType", "Attribute:href", "Attribute:rel"}, {"Table.title", "Table.description", "Table.presenter", "Table.duration", "Table.start", "Table.keywords", "Table.published", "Table.visibility", "Table.url", "Table.status", "Table.active", "Table.created", "Table.lastUpdated", "Table.link", "Table.Attribute:id", "Table.syndicationType", "Table.Attribute:href", "Table.Attribute:rel"}),
    myTest = List.Generate( ()=>
        [Result = Source, Counter = 0],
        each [Result][[Attribute:href] <> null and [Counter]< 20,
        each [Result = Xml.Tables(Web.Contents([Attribute:href])),
        Counter = [Counter] + 1]

    )
in
    myTest

     

    Any pointers you could provide in getting this right would be greatly appreciated! 

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      Hi Anonymous 

      what List.Generates allows you is to reference an item from the previous row/item/instance (of your iteration).

      But as you've mentioned, you haven't utilized this yet.

      In the 3rd arugment you're defining a record that will by default be available for future reference. Every field in there can simply be referenced by its field name. So to reference the field "Result", you'd write [Result], as this is the lookup operator for records.

       

      Try something like this: 

       

      each [Result = Xml.Tables(Web.Contents( Table.LastN( [Result], 1)[Attribute:href]))

       

      [Result] references the previous item Result filed. It seems to be a table from which you need the last row.

      Table.LastN(..., 1) will return this.

      [Attribute:href] as a lookup operator will retrieve the value from this exact column.

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi ImkeF ,

         

        Thank you so much for your help with this!  I've been reading through more of the resources that you suggested and I've tweaked the code slightly so that it's hopefully a little more efficient.

         

        First, I've created a function that accepts a URL as a string and returns the result:

         

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

         

        Then I have written a query that invokes the function.  It executes without any errors but only returns a list with one table in it.  I tried your code but I think the Web.Contents part was causing a problem so I have removed it but I'm not sure if the syntax is going to work.  The code now  looks like this:

         

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

         

        Thank you once again for your help and advice!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ImkeF ,

       

      I hope you're safe and well.

       

      If I use the approach of passing a URL to a function and the function returning the dataset, am I able to reference fields within that dataset without expanding the table?  I can't seem to get the code to recognise the field name. For instance if I use the field name reference [Attribute:href] the Attribute section is underlined as an Invalid Identifier error.

       

      Many thanks for your 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?