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
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

Yes, I can understand you assesment, but this shouldn't be the cause. Pls check the following query that paginates through 3 webpages using this method successfully:

 

let
Pagination = List.Skip(List.Generate( () => [Result = Web.Page(Web.Contents("http://www.finanzen.net/aktien/US-Aktien-Realtimekurse@intpagenr_"&Text.From(Counter)))[Data]{0}, Counter = 0], // Start Value
			each 	[Counter] <=3, // Condition under which the next execution will happen
			each	[Result = Web.Page(Web.Contents("http://www.finanzen.net/aktien/US-Aktien-Realtimekurse@intpagenr_"&Text.From(Counter)))[Data]{0}, // retrieve results per call   
			        Counter = [Counter]+1	], // determine the LastKey for the next execution
			each	[Result]
),1),
    Combine = Table.Combine(Pagination)
in
    Combine

If you find a website where we could harvest  the "next page" in the results returned, pls forward and we can test your scenario there.

 

kroll's avatar
kroll
Frequent Visitor
9 years ago

Imke, thank you for the example. I agree, it works.

I'll try to play around with both versions of the code to make it work!

I hope to be back with my findings in a few days.

Thank you for your help!