Forum Discussion
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...
Okay, so I'm the OP on this and unfortunately I lost track of this post for a couple YEARS... whoops.
But now I got back to it and in looking through all the responses, it seems to be a very hot topic.
My original question was about HubSpot CRM specifically and pagination in general.
I have finally found a solution, which I will post here for everyone else's benefit.
But first, a couple notes:
- My goal here was to get DEAL info from HubSpot CRM. Presumably, the same process would work for any other info from HubSpot like CONTACTS or COMPANIES. Also would probably work for many others who have a similar use case.
- Like many APIs out there, they PAGINATE the results, by default they will give you 100 per API call, but you can request up to 250 per call.
- Like many APIs that paginate, they also give you an OFFSET, so you provide that offset on subsequent calls to get additional pages.
- Like many APIs that paginate, they will give you a boolean indicator if there are more or not.
- Like many APIs out there, the HubSpot API breaks data up into separate API calls, so the first one gets you a list of IDs, which you then must use to make a subsequent call for each individual item.
- Like many APIs out there, the times sent are in unix timestamps. My query converts these to regular time, albeit hard-coded to Eastern Daylight Time.
- I believe all the fields I'm using in this are standard HubSpot fields, but there is a possibility there are some custom ones. If so, then you may get an error and have to remove those.
- I use two HubSpot API endpoints here, the first one that gives me a list of ALL DEALS with their dealId is https://api.hubapi.com/deals/v1/deal/paged. The second one that gives me individual deal properties is https://api.hubapi.com/deals/v1/deal/[dealId].
- Be aware that depending on your number of DEALS, this can take a really long time to execute. It is definitely not "efficient" and is not optimized in any way. I have over 4,000 deals and it takes maybe a half-hour or so.
- Also, be aware that HubSpot has a daily limit on API calls of about 40,000. So running this query with 4,000 deals will eat up a lot of your API calls.
- I believe I can solve #7 and #8 above by using some combination of two things to optimize this query:
- Specifying specific PROPERTIES in the initial API call may prevent me from having to make a separate call for each individual deal, if I know the exact properties I want
- Instead of using the above API endpoint for ALL DEALS, I can use the one for RECENTLY CREATED DEALS and specify a start date. This endpoint is https://api.hubapi.com/deals/v1/recent/created.
At any rate, with all the above caveats, here is the query which works for me and includes all the expanded tables/fields to get to the individual values.
None of the other solutions worked for me, but I used several of them to get to this point. Thanks to everyone for your help.
If I am able to speed this up using the items in #10 above, I will post that solution as well.
let
// Start Values
Pagination = List.Skip(List.Generate( () => [hsOffset = 0, Counter = 0, isMore = true],
// Condition under which the next execution will happen
each [isMore] <> false,
// retrieve results per call
each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged?hapikey=[ENTER-YOUR-API-KEY-HERE]&limit=250&offset=" & Text.From(hsOffset))),
hsOffset = try [WebCall][offset] otherwise 0,
isMore = if [Counter] <1 then null else [WebCall][#"hasMore"],
// internal counter
Counter = [Counter] + 1
// ,Table = Table.FromRecords(WebCall[deals])
]
// ,each [Table]
),1),
#"Converted to Table1" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"WebCall", "hsOffset", "isMore", "Counter"}, {"Column1.WebCall", "Column1.hsOffset", "Column1.isMore", "Column1.Counter"}),
#"Expanded Column1.WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.WebCall", {"deals", "hasMore", "offset"}, {"Column1.WebCall.deals", "Column1.WebCall.hasMore", "Column1.WebCall.offset"}),
#"Expanded Column1.WebCall.deals" = Table.ExpandListColumn(#"Expanded Column1.WebCall", "Column1.WebCall.deals"),
#"Expanded Column1.WebCall.deals1" = Table.ExpandRecordColumn(#"Expanded Column1.WebCall.deals", "Column1.WebCall.deals", {"dealId", "isDeleted"}, {"Column1.WebCall.deals.dealId", "Column1.WebCall.deals.isDeleted"}),
// THIS IS THE SECOND API CALL FOR DEAL DETAILS
#"Added Custom" = Table.AddColumn(#"Expanded Column1.WebCall.deals1", "DealInfo", each Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/" & Text.From([Column1.WebCall.deals.dealId]) & "?hapikey=[ENTER-YOUR-API-KEY-HERE]"))),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1.WebCall.offset", Int64.Type}, {"Column1.WebCall.hasMore", type logical}}),
#"Expanded DealInfo" = Table.ExpandRecordColumn(#"Changed Type", "DealInfo", {"properties"}, {"DealInfo.properties"}),
#"Expanded DealInfo.properties" = Table.ExpandRecordColumn(#"Expanded DealInfo", "DealInfo.properties", {"dealname", "createdate", "hubspot_owner_id", "hs_analytics_source", "deal_temperature", "hs_createdate", "dealtype", "closed_lost_reason", "closedate", "pipeline", "notes_last_contacted", "dealstage", "hs_analytics_source_data_2", "hs_analytics_source_data_1", "amount"}, {"DealInfo.properties.dealname", "DealInfo.properties.createdate", "DealInfo.properties.hubspot_owner_id", "DealInfo.properties.hs_analytics_source", "DealInfo.properties.deal_temperature", "DealInfo.properties.hs_createdate", "DealInfo.properties.dealtype", "DealInfo.properties.closed_lost_reason", "DealInfo.properties.closedate", "DealInfo.properties.pipeline", "DealInfo.properties.notes_last_contacted", "DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount"}),
#"Expanded DealInfo.properties.dealstage" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties", "DealInfo.properties.dealstage", {"value"}, {"DealInfo.properties.dealstage.value"}),
#"Expanded DealInfo.properties.hs_analytics_source_data_2" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealstage", "DealInfo.properties.hs_analytics_source_data_2", {"value"}, {"DealInfo.properties.hs_analytics_source_data_2.value"}),
#"Expanded DealInfo.properties.hs_analytics_source_data_1" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_2", "DealInfo.properties.hs_analytics_source_data_1", {"value"}, {"DealInfo.properties.hs_analytics_source_data_1.value"}),
#"Expanded DealInfo.properties.amount" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source_data_1", "DealInfo.properties.amount", {"value"}, {"DealInfo.properties.amount.value"}),
#"Expanded DealInfo.properties.notes_last_contacted" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.amount", "DealInfo.properties.notes_last_contacted", {"value"}, {"DealInfo.properties.notes_last_contacted.value"}),
#"Expanded DealInfo.properties.pipeline" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.notes_last_contacted", "DealInfo.properties.pipeline", {"value"}, {"DealInfo.properties.pipeline.value"}),
#"Expanded DealInfo.properties.closedate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.pipeline", "DealInfo.properties.closedate", {"value"}, {"DealInfo.properties.closedate.value"}),
#"Expanded DealInfo.properties.closed_lost_reason" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closedate", "DealInfo.properties.closed_lost_reason", {"value"}, {"DealInfo.properties.closed_lost_reason.value"}),
#"Expanded DealInfo.properties.dealtype" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.closed_lost_reason", "DealInfo.properties.dealtype", {"value"}, {"DealInfo.properties.dealtype.value"}),
#"Expanded DealInfo.properties.hs_createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.dealtype", "DealInfo.properties.hs_createdate", {"value"}, {"DealInfo.properties.hs_createdate.value"}),
#"Expanded DealInfo.properties.deal_temperature" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_createdate", "DealInfo.properties.deal_temperature", {"value"}, {"DealInfo.properties.deal_temperature.value"}),
#"Expanded DealInfo.properties.hs_analytics_source" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.deal_temperature", "DealInfo.properties.hs_analytics_source", {"value"}, {"DealInfo.properties.hs_analytics_source.value"}),
#"Expanded DealInfo.properties.hubspot_owner_id" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hs_analytics_source", "DealInfo.properties.hubspot_owner_id", {"value"}, {"DealInfo.properties.hubspot_owner_id.value"}),
#"Expanded DealInfo.properties.createdate" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.hubspot_owner_id", "DealInfo.properties.createdate", {"value"}, {"DealInfo.properties.createdate.value"}),
#"Expanded DealInfo.properties.dealname" = Table.ExpandRecordColumn(#"Expanded DealInfo.properties.createdate", "DealInfo.properties.dealname", {"value"}, {"DealInfo.properties.dealname.value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded DealInfo.properties.dealname",{{"DealInfo.properties.createdate.value", Int64.Type}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"DealInfo.properties.closedate.value", Int64.Type}, {"DealInfo.properties.notes_last_contacted.value", Int64.Type}, {"DealInfo.properties.hs_createdate.value", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Create.DateTime", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -4, 0, [DealInfo.properties.createdate.value]/1000)),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Create.DateTime", type datetime}})
in
#"Changed Type3"
I am looking for a pagination solution too, and I am almost there but not quite....!
here is my code (and it feels very close):
let
baseURL = "https://api.vworkapp.com/v4/jobs.xml",
apiKey = "?api_key=xxxxxxxx",
params = "&start_at=2018-10-15&end_at=2018-10-16",
perPage = "&per_page=200",
queryURL = baseURL & apiKey & params & perPage,
PreFetchData = Xml.Tables(Web.Contents(queryURL)),
PageCount = Number.FromText(PreFetchData{0}[#"Attribute:total_pages"]),
GetPageData = (Index) =>
let
XMLData = Xml.Tables(Web.Contents(queryURL & "&page=" & Index))
in
XMLData,
Data = List.Generate ( () =>
[i=1],
each [i] < PageCount,
each [
i=[i]+1,
ResList = GetPageData(
Number.ToText([i])
)
],
each [ResList]
)
in
Data What this gets me is:
The Table records are spot on, I can expand those and they are exactly what I want, but there is an error in the first list item and I can't figure out why:
I can't seem to get past this error, can anyone put me on the right track to solving why i=1 would be a problem? The api accepts page=1 so I don't see what would be wrong....
EDIT: I have skipped over that row and everything else after that works as expected, so if I solve why i=1 is an issue then I am done!
Anonymous looks to me that you are making it too complex...i think , in your case, u can paginate simply by calling a function. See this page
http://sqlcodespace.blogspot.com/2017/09/power-bipower-query-api-response.html
Thank you Reddy. That was the most elegant solution I found in my use case and worked like a charm.
