I'm currently using this query in MS Power Query, you can copy and paste, I've substituted the 'demo' key for mine.
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
each [Last_Key] <> null, // Condition under which the next execution will happen
each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=demo&count=250")) else Json.Document(Web.Contents("https://api.hubapi.comts/all/contacts/all?hapikey=demo&count=250&vidOffset="&Last_Key&"")), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1),
Pagination1 = Pagination{0},
Value = Pagination1[Value],
contacts = Value[contacts]
in
contacts
I can not make it paginate. One issue I'm having is if I declare [offset] to [vid-offset] I can an invalid identifier error. I believe this is the source of my problem but can't figure out a workaround. Would appreciate the help,
Notice the change in the URL parameter from "vidOffset" to "vid-offset". This should resolve the issue of the invalid identifier error and allow you to paginate through the API results.
Apart from this, you can adjust the count parameter to increase or decrease the number of contacts returned per API call. This can help optimize the pagination process and reduce the number of API calls required to retrieve all of the results.
I hope this helps! Let me know if you have any further questions.
let
baseuri = baseurlContacts & apikey,
initReq = Json.Document(Web.Contents(baseuri)),
initData = initReq[results],
//We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,
//then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
//create a table from those records
gather = (data as list, uri) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(uri))[paging][next][after],
//build new uri using the original uri so we dont append offsests
newUri = baseuri & "&after=" & newOffset,
//get new req & data
newReq = Json.Document(Web.Contents(newUri)) ,
newdata = newReq[results] ,
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if Table.Contains ( Record.ToTable(newReq) , [Name = "paging"] ) = true then @gather (data , newUri) else data
in check,
//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if Table.Contains ( Record.ToTable (initReq) , [Name = "paging"] ) = true then gather( initData , baseuri ) else initData ,
//then place records into a table. This will expand all columns available in the record.
expand = Table.FromRecords(outputList)
in
expand
hi @a2 , Any idea how to use your example in the Power BI Service? As it's dynamic, I'm guessing you need to use the relative path feature of Web.Contents but I'm struggling to see where to implement this? Thanks
Thanks @snamuth this is really useful. It also works for the companies API. Just a quick note that if you want this to work in the PowerBI service you will need to use the relative path feature of Web.Contents as below
each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com",
[
RelativePath="companies/v2/companies/paged?hapikey=*****&limit=250&properties=name&properties=city&offset=" & Text.From([Last_Key])
]
)),
One thing I have noticed is that this seems to return duplicate companies (each company appears twice). Also when debugging this query PowerBI is calling the API twice each loop (ie. each offset is called twice). Do you have any idea why this is?
Hi @Brodie, happy to help. While I'm not familiar with MS Power Query / how it functions, I'll try to help in any way I can. If you're trying to access the vid-offset value, have you tried using ["vid-offset"] with quotes? I recently built a tool in NodeJS to paginate through contacts in a HubSpot Account to push the values from a a specific property into an array and count them. I had the same issue of accessing the has-more and vid-offset parameters, but not sure if this is exactly what you're seeing. Here's my repo: https://github.com/cbarley10/hubspot-pagination
.png "HubSpot Alumni"){kind=icon}
