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!

[Greg_Deckler]

I know that @ImkeF has done things like this.

[ImkeF]

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

[Anonymous]

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! 

[Anonymous]

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]

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?

[Anonymous]

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:

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

[ImkeF]

Hi @Anonymous ,

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

[Anonymous]

Hi @ImkeF,

No luck I'm afraid.  This is the code now but I still get an Invalid Identifier error for the Attribute part.

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

[ImkeF]

Hi @Anonymous ,

for debugging purposes, please use this query:

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

It should create a list and there you have to navigate to the errors and paste screenshots of them please.

[Anonymous]

Hi @ImkeF ,

No problem, I used the query and got the following:

Drilling through the first row gives this:

Drilling through on the error rows gives this:

The error is the same for all the subsequent rows...

[ImkeF]

Hi @Anonymous ,

so you have to navigate to the link table first.

Please try this:

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result]{[Name = "link"]}, 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

[Anonymous]

Hi @ImkeF ,

I've tried the code you suggested and although it's thrown an error I think we're making progress!

The first row returns data as expected but the subsequent rows show this error:

[ImkeF]

Hi @Anonymous ,

yes, definitely making progress here.

Please check format of paste picture of myList{0}.

If it is a record, then the following code might work:

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result][link], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

[Anonymous]

Hi @ImkeF ,

The data in myList{0} looks like this:

Forgive my ignorance as I'm a bit new to Power Query data structures.  Is the structure essentially a table with two rows (records) in it and the Table column then holding a Table data structure within it?

I tried  the ammended code which still errored but gave this message :

[ImkeF]

Oh yes, I must have been sleeping a bit here.

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result]{[Name = "link"]}[Table], 1)[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

[Result] returns a table

{[Name = "link"]} grabs the row from that table where Name = "link" : This is a record.

[Table] grabs the value from the Table-field of the record. In there sits the table that we're interested in.

[Anonymous]

Hi @ImkeF ,

Ah I see!  I didn't realise that you could nest references in that way!

So, a little more progress I think, still erroring but with this message from row 2 onwards:

[ImkeF]

Hi @Anonymous ,

one day, we'll get there 😉

let
myList = List.Generate( ()=>
    [Result = Query4("https://api.brighttalk.com/v1/channel/10737/webcasts"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  Query4(Table.LastN([Result]{[Name = "link"]}[Table], 1){0}[#"Attribute:href"]), Counter = [Counter]+1],
    each [Result])
in
    myList

[Anonymous]

Hi  @ImkeF,

Any chance you can also help me, very similar issue to this thread but I have to drill down a few steps to get my 'next' url

Function GetURL:

(url as text) as table =>
let
    Source = Json.Document(Web.Contents(url)),
    results = Source[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "properties", "createdAt", "updatedAt", "archived"}, {"id", "properties", "createdAt", "updatedAt", "archived"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"amount", "createdate", "dealstage", "hs_lastmodifieddate", "hs_object_id", "printing_kpi_by"}, {"amount", "createdate", "dealstage", "hs_lastmodifieddate", "hs_object_id", "printing_kpi_by"})//& Number.ToText(after))),
in
   #"Expanded properties"

the result if just requesting the first URL & not drilling down (apologies for using a table but having errors every time I try to load screenshots):

Name	Value
results	List
paging	Records

The next URL is within the 'paging' records, the following steps are required to get to it:

let
source = Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/deals?limit=100&paginateAssociations=false&archived=false&hapikey=APIKEY&properties=dealstage&after=0")),
    #"Converted to Table" = Record.ToTable(source),
    Value = #"Converted to Table"{1}[Value],
    next = Value[next],
    #"Converted to Table1" = Record.ToTable(next)

in
#"Converted to Table1"

Which returns the following table:

Name	Value
after	1677291168
link	https://api.hubapi.com/crm/v3/objects/deals?archived=false&paginateAssociations=false&limit=100&afte...

This is the code I am trying to tweak based on this thread, I am just unsure how to reference the next link correctly 

let
myList = List.Generate( ()=>
    [Result = GetURL("https://api.hubapi.com/crm/v3/objects/deals?limit=100&paginateAssociations=false&archived=false&hapikey=APIKEY&properties=dealstage&after=0"), Counter = 0],
    each  [Counter] < 5,
    each [Result =  GetURL(Table.LastN([Result]{[Name = "link"]}[next], 1){0}[#"Attribute:value"]), Counter = [Counter] +1],
    each [Result])
in
    myList

It returns a list of 5, row 1 table pulls through correctly as 'Table' the next 4 show as errors.

"Expression.Error: the key didn't match any rows in the table

Details:

 Key = 
   Name=link

   Table=[Table]

Any help would be greatly appreciated,

Thanks!

@ImkeF

[Anonymous]

Hi @ImkeF ,

I think that day has arrived!  We have success!!! 

I now get a result set of Tables as expected:

Can I ask one more question please? Ultimatley I need to replace the Counter variable with a test for whether a link row is returned (the last paginated dataset won't have a link row).  How would it be best to do that?

Many many thanks for all your help with this 😁

[ImkeF]

Hi @Anonymous ,

you can use the "brute force" method you used before (try ... otherwise). Otherwise you'd have to split up your statements: https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/ 

Simply delete the counter statement - that was just for debugging purposes.