Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |