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.