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
kroll's avatar
kroll
Frequent Visitor
9 years ago

There is still a problem with that code... It returns the first pass (1st 100 records).

I've changed the 'limit' to 10, to make the issue more visible.

 

Thoughts?

//Previous code with access credentials 
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 [ 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
     			Last_Key = if [Counter]<=1 then "20170404130408053410572" else WebCall[lastKey] ,// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value] // last step of your further queries
                      ], 
   		each [Value]),1) // Select just the Record of the last step from your query
in
Pagination
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

Yes, the limit-parameter will do that. If you set it to 1000, 701 rows will be returned.

Is this what you expect?

  • kroll's avatar
    kroll
    Frequent Visitor
    9 years ago

    Sorry, I was not clear in my comment. I'm trying to point out that the code does not paginate as expected.

     

    If a dataset has more record than the limit, the next page should have the records that follow the previous page, until all records are loaded. lastKey from the first load has the start value for the next "page".

    As you pointed out, there are over 700 records, but the the code returns only the first page.

     

    If you run the query without 'limit' parameter, 50 records (that's the default) will be return. The code should use lastKey to pull the next page, but it doesn't.

    Do you have any ideas what could be the problem with the code?

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    9 years ago

    How many records to you expect? (unique id's)

  • kroll's avatar
    kroll
    Frequent Visitor
    9 years ago

    Imke, I had a feeling that there was a disconnect between what we were seeing...

    The bottom line - you are GREAT, and your code is correct, and I appreciate your help VERY MUCH!

    Thank you!!!

     

    More details, in case others repeat my mistake...

    Regardless of the setting on 'limit=', the returned number of records is the same. Basically, your query works exactly as expected!

    My confusion was caused by the fact that expanding content of the 'Error' in the initial table would show the records pulled by the first pass (w/o pagination).

     

    I should have paid more attention to your instructions "just expand the record (& ignore the error-message for a start): Transfer the list to a table & then you can expand the records you need.".

     

    It works great.

    Once again, THANK YOU!!!