how to create a query that paginates?

[gotmike]

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 results.

For instance, if you send:

https://api.hubapi.com/deals/v1/deal/all?hapikey=demo

at the very end of the query, you see the following JSON:

"hasMore":false
"offset":27939158

so, if hasMore is true, the NEXT query should look like this:

https://api.hubapi.com/deals/v1/deal/all?hapikey=demo&offset=27939158

and then, we would want to repeat the process until hasMore comes back with false.

i'm completely new to power bi, so would love to know how to handle this type of query process.

in another language, this would just be do { } while (hasMore == false);

or something like that...

[Anonymous]

@ImkeF - I have been following your guidance throughout this thread, your help has been really insightful (and a huge benefit to the Power BI community).  However, I am still struggling with how to isolate the value that gives me the next page for the API call, here is the response from the API:

{
"metadata": {
"filtering": {},
"paging": {
"per_page": 100,
"current_page": 2,
"next_page": 3, <-- Need this value
"prev_page": 1,
"total_pages": 81,
"total_count": 8001
},
"sorting": {
"sort_by": "updated_at",
"sort_direction": "DESC NULLS LAST"
}
},
"data": [
{...}]

In your code from the prior entries, you suggested doing this:

let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
each try Record.Field([WebCall],"???next_page???")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://api.sample.com/v2/activities/actions.json?per_page=100&include_paging_counts=true&page="&Text.From([Page])&"",[Headers=[Authorization="<APIKEY>"]])), // retrieve results per call
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
)),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"Column1.WebCall", "Column1.Page", "Column1.Counter"})
in
#"Expanded Column1"

My question is the "try Record.Field([WebCall]."next_page") step, how do I grab the value under metadata/paging/next_page in the json?  My Power M game is not that strong.

[ImkeF]

Hi @Anonymous  

you can navigate JSON records fields with square brackets like so:

#"Parsed JSON"[metadata][paging][next_page]

 please see attached file

[Anonymous]

@ImkeF - I spoke too soon.  I am having trouble with the following line from the code you gave in previous posts:

let
Pagination = List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value

   		each try Record.Field([WebCall],)<>null otherwise false or [Counter]=0,  //how do I reference the [metadata][paging][next_page] value in this expression?


   		each [ WebCall = Json.Document(Web.Contents("https://api.sample.com/v2/activities/stuff.json?per_page=100&include_paging_counts=true&page="&Text.From([Page])&"",[Headers=[Authorization=""]])), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
))
in
    Pagination

Currently, when I run this it fails to return even the first page of data.  Sorry, I had to remove the API key and real URL, but the structure is similar to the post above.

[ImkeF]

Hi @Anonymous 

please try:

... Record.Field( [WebCall] [metadata][paging], "next_page" ) ....

[Anonymous]

Hi, @ImkeF  I have gone through the entire post and successfully able to paginate the report. But I am facing this strange issue where my query keeps on running even if it hits the last records.

Below is my query what am I doing wrong here?

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
each List.Count([WebCall][result])>0 or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Page])]])),
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"}, {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"})
in
#"Expanded result1"

[ImkeF]

Hi @Anonymous 

this could be due to the result list not being actually empty. Could it be that you need to navigate further down to it?
Otherwise please paste screenshot of the empty list in the allegedly empty items. (add "or [Counter] < YourNumber  to stop the execution at a feasable number)

[Anonymous]

Hi @ImkeF  Thanks for your reply

My query keeps on running even if I change the counter parameter. There are close to 6000 records in the table but query runs beyond these.

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0], // Start Value
each List.Count([WebCall][result])>0 or [Counter]<3, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Page])]])),
Page = [Page]+1,
Counter = [Counter]+1// internal counter
]
) ,1),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Extracted Values" = Table.TransformColumns(#"Expanded WebCall", {"result", each Text.Combine(List.Transform(_, Text.From)), type text}),
result = #"Extracted Values"{0}[result]
in
result

Also not sure how to add images here

[ImkeF]

Hi @Anonymous 

you have to adjust the condition like so for debugging purposes:

each List.Count([WebCall][result])>0 and [Counter]<3, // Condition under which the next execution will happen

click the camera-icon to add pictures:

[Anonymous]

Hi @ImkeF ,

I was able to run the query successfully suing the condition each List.Count([WebCall][result])>0 and [Counter]<3.

Now my question is how I make sure that it runs till the last record in the table. Here are the images 

[ImkeF]

Hi @Anonymous 

Question is how this looks like for the first item that shall cause the iteration to stop:

Will we see a 0 in Page for example? Or a null?

Then you'd adjust your condition like so:

each [WebCall][result] {0}[Page]>0 and [Counter]<3

This retrieves the first element from the list (a record) and gets the figure in field "Page".

For some reason, your API doesn't return an empty list, you have to find out what else there is to build the condition on.

[Anonymous]

Thanks, @ImkeF  and for others, this might help. I made some changes to the query and now it is running as expected to return the total number of records from the table.

Below is my query

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0],
each List.Count([WebCall][result])>0 or [Counter]=0,
each [ WebCall = Json.Document(Web.Contents("https://xxx.service-now.com/api/now/table/task?sysparm_limit=100&sysparm_offset=1",
[Query=[sysparm_offset =Text.From([Counter])]])), Page = [Page]+1, Counter = [Counter]+100]
) ,1
),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
#"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"}, {"sys_updated_on", "number", "sys_created_on", "closed_at", "active", "opened_at", "business_duration", "assignment_group"})
in
#"Expanded result1"

[Anonymous]

@ImkeFI knew it was something simple, thank you! 

[ImkeF]

Hi @Anonymous ,

thanks and happy to help. But the JSON you've posted isn't valid.

Please paste valid JSON and I will follow this up.

Thanks.

[Anonymous]

@ImkeF - Sorry about that, forgot the closing brackets.

{
    "metadata": {
        "filtering": {},
        "paging": {
            "per_page": 100,
            "current_page": 2,
            "next_page": 3,
            "prev_page": 1,
            "total_pages": 81,
            "total_count": 8034
        },
        "sorting": {
            "sort_by": "updated_at",
            "sort_direction": "DESC NULLS LAST"
        }
    },
    "data": [
        {
            "id": 4926173,
            "to": "XXXXXXXXXXX",
            "duration": 106,
            "sentiment": "5-Referred to Other Contact",
            "disposition": "1-Talked to Contact",
            "created_at": "2020-02-26T16:44:37.875353-05:00",
            "updated_at": "2020-02-26T16:44:38.109183-05:00",
            "recordings": [
                {
                    "url": "https://sample.com",
                    "status": "completed",
                    "recording_status": "completed"
                }
            ],
            "user": {
                "_href": "https://sample.com",
                "id": 1555
            },
            "action": null,
            "called_person": {
                "_href": "https://sample.com",
                "id": 699379
            },
            "crm_activity": {
                "_href": "https://sample.com",
                "id": 23514150
            },
            "note": {
                "_href": "https://sample.com",
                "id": 1784343
            },
            "cadence": null,
            "step": null
        }
    ]
}

  Thanks! 

[Anonymous]

Thanks a lot @ImkeF.Working perfect now.

[DBa]

Thanks Imke

Additionally, in the case where some rows were error-ing out, taking out 

each [Table]

Allows the step by step expansion of the columns, which no longer contains error Rows.

I suspect the issue is Table.FromRecords in the below code:

Table.FromRecords(WebCall[events])

When there are record/columns within the API that are blank, Table.FromRecords doesn't know how to treat them and hence creates a whole row of errors.

Thanks for all the help!

[DBa]

1) I think I mentioned it but it was lost in the long comment - the change you previously suggested gives a ''We cannot convert a value of type List to type Table.' error. I've been trying the same idea but I am not sure what else to cut out (also because I'm only at 75% understanding on how the whole query works - I don't understand some of the little details what they do)

2)errors out altogether - I've just noticed now but even though 'converted to table' works, when i click on the 'Pagination' step, the name is 'Error': 

would it work to add an error check statement in this part of the query :

each  Table.RowCount([Table])>0 or [Counter]=0  + or [Table] is error?  I already tried to remove last row from Table = Table.FromRecords(WebCall[events]) but it doesn't do anything at all 

Thanks for the effort!

[Grant]

Hi Imke

Absolutely brilliant. That is exactly what I needed. Thank you for your assistance and rapid turnaround.

Kind Regards - Grant

[cartman21]

Hi,

I've been trying to solve my query using this thread, but not really sure how to go about it.

Basically, the database I'm connected to cuts off values at 500 per query, so im doing 0-500, then 500-1000, then 1000-1500 and so on.

Here is the code (with changed url/api key for security reasons)

let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start=0&limit=1499&api_token=xxxxxx")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "owner_id", "name", "open_deals_count", "re "Column1.cc_email"})
in
#"Expanded Column1"

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

As you see, in the data above, I try to query the data from start=0 to limit=1499, but i still only get values 0 to 500. I have about 1400 values in the actual data. 

Any help would be much appreciated.

[cartman21]

If anyone can let me know, it would be amazing, thanks a lot!

[ImkeF]

Hi @cartman21,

if you know beforehand that you need to split up your calls into 3 chunks and don't have to retrieve values from the current call to make the next one, you can use a much simpler logic than the List.Generate-versions in this thread.

You start with creating a table that holds all the different URL-parameters that you then pass on to a function. Expand that resulting table & you're done:

let
// table with your query intervalls    
Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}}),
// Call Function    
CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish])))
// Function
Function = (Start as text, Finish as text) =>
let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Limit&"&api_token=xxxxxx")),data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "owner_id", "name", "open_deals_count", "re "Column1.cc_email"})
in
#"Expanded Column1"
in
CallFunction