Cursor Paginated data from Web API

[Anonymous]

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 connecting through a Web datasource) the Navigator returns a table with two rows, the first row that contains a table of data that I'm requesting and a second row that contains a table that contains the original url but with a cursor parameter appended to it. 

so the structure looks like this:

Original api call = https://api.myapiurl.com/v1/channel/1234/webcasts

which produces:

Name	Table
webcast	Table
link	Table

The link table contain the following:

href	rel
https://api.myapiurl.com/v1/channel/1234/webcasts?cursor=6789	next

I can expand the webcast row which contains the data without any problems but how do I then call the next page of data and store it with the first call and then continue until I have all the available data?

Many thanks!

[ImkeF]

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]

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!