cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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 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:

 

NameTable
webcastTable
linkTable

 

The link table contain the following:

 

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

 

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!

22 REPLIES 22
Super User IV
Super User IV

I know that @ImkeF has done things like this.

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes, there is a lot of methods in this thread: https://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/td-p/20047

Strongly recommend this video as well: https://www.youtube.com/watch?v=vhr4w5G8bRA

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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 @eyeballkid 

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.

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

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 @eyeballkid ,

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?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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:

Image1.jpg

 

Expanding the Table column gives me this:

Image2.jpg

 

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:

Image3.jpg

 

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):

 

Image4.jpg

 

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

Many Thanks

 

 

 

Hi @eyeballkid ,

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors