Forum Discussion
Cursor Paginated data from Web API
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!
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!
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?
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 SourceIf I pass the following url:
https://api.myWebService.com/v1/channel/12345/webcastsI 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
Hi Anonymous ,
try to delete the XML.Tables(Web.Contents( -part of the query.
