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: NameTablewebcastTablelinkTable The link table contain the following: hrefrelhttps://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!

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): NameValueresultsListpagingRecords 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:NameValueafter1677291168linkhttps://api.hubapi.com/crm/v3/objects/deals?archived=false&paginateAssociations=false&limit=100&after=1677291168&properties=dealstageThis 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 tableDetails: Key = Name=link Table=[Table]Any help would be greatly appreciated,Thanks! ImkeF

I know that ImkeF has done things like this.

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

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

Cursor Paginated data from Web API | Microsoft Fabric Community

23 Replies

Greg_Deckler
Community Champion
6 years ago
I know that ImkeF has done things like this.
- ImkeF
  Community Champion
  6 years ago
  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
    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!
ChaitanyaSai
Regular Visitor
1 year ago
Hi Team,
I have an issue while fetching the Data from Cursor Based Web API, Could Some help Me with the M-Script with header value. I have tried with Some Which is causing me a Pop of Authentication with the Manual Refresh as Below and I am unable to see the Data Sources after publishing it into the POwerBI Services.
Appreciated if supported.
thank you .

Forum Discussion

Cursor Paginated data from Web API

23 Replies

Related content

Dynamic Data Source Error / RelativePath / Cursor Pagination / Headers

Web based cursor pagination issue

Paginated Report

API REST Tracket Cursor Based Pagination

Paginated report

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

Name	Table
webcast	Table
link	Table