Hubspot Private Apps - Powerquery pagination

Hey 🙂

I kwno what you mean regarding the last page of contacts, used to have a really long winded work around fo rit. but the below is my my current (slickest solution)

let
baseuri = "https://api.hubapi.com/crm/v3/objects/companies?limit=100&" & "properties=name&properties=lifecyclestage",
headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer YOUR PAT TOCKEN"]],

initReq = Json.Document(Web.Contents( baseuri, headers)),
#"Converted to Table" = Record.ToTable(initReq),
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, headers))[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, headers)) ,
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),
#"Removed Other Columns" = Table.SelectColumns(expand,{"id", "properties"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "properties",
Record.FieldNames(#"Removed Other Columns"{0}[properties]),
Record.FieldNames(#"Removed Other Columns"{0}[properties]))
in
#"Expanded Custom"