Forum Discussion
gotmike
10 years agoFrequent Visitor
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...
ImkeF
Community Champion
9 years agoPls check this code:
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", Counter=0], // Start Value
each [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
each [ Last_Key = try if [Counter]<=1 then "20170404130408053410572" else [WebCall][lastKey] otherwise null,// determine the LastKey for the next execution
WebCall = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=10&offset="&Last_Key&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"hits"}, {"hits"}),
#"Expanded hits2" = Table.ExpandListColumn(#"Expanded Column3", "hits"),
#"Expanded hits3" = Table.ExpandRecordColumn(#"Expanded hits2", "hits", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"})
in
#"Expanded hits3"
kroll
9 years agoFrequent Visitor
In this thread it's a comment that is repeated a lot:
Imke, you are great!
Thank you for your help!!!