Forum Discussion

gotmike's avatar
gotmike
Frequent Visitor
10 years ago

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

in your web call - in second part(else part) you are giving a variable inside web.contents - which is not supported in power bi service for scheduled refresh.

 

Thanks to excellent blog by Chris Webb - this can be solved - or lets say there is a way to overcome this issue.

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

 

In your case, you would need to prvide a fixed value here instead of "&Last_Key&" 

"https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset="&Last_Key&""))

 with query paramters as folow

Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=someFixedValue",Query=[offset=Last_Key])
 

This someFixedValue has to be some valid value which works fine - say your first value of key - which will be used as a dummy value only to 'trick' the PBI service.

BR

emudria.

iggyvic's avatar
iggyvic
Frequent Visitor
7 years ago

I got it to work by adding extra [] brackets. 

 

Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=0",[Query=[offset=Last_Key]])),

 

Thanks!

 

--- OLD POST ---

 

Thanks for getting back to me!

 

I've tried your formula but am still getting errors. 

 

WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY")) else Json.Document(Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=0",Query=[offset=Last_Key])), // retrieve results per call

Basically the first 100 rows (when counter <1) always load. But then regardless of what fixed value I set, even with no fixed value, the 101 row returns an error.

 

Likewise, if I input the URL in the browser offset=0 returns values (probably the same 100 first that's also loaded into Power BI) and anything else I've tried >0 returns error. If I set offset=1, I get "{"error":{"type":"INAVLID_OFFSET_VALUE","message":"The value of offset 1 is invalid"}}