PowerQuery (Excel results) prob with Pagination in Deals API

[bdesilva]

I'm new to both the HS API and Microsoft Power Query which is part of Excel and more. It was easy to grab the deals using the API but the limit of 250 messes me up. I cannot figure out how to code; I think it's called M-code, in PQ to do the pagination. Any help or even better samples would be amazing. What follows is my existing code minus my API key. All of it was generated by my manipulation of the query results and not hand coded.

----------------------------

let

    Source = Json.Document(Web.Contents("https://api.hubapi.com/deals/v1/deal/paged?hapikey=hidden&limit=250&properties=dealname&properties=c...")),

    #"Converted to Table" = Record.ToTable(Source),

    Value = #"Converted to Table"{0}[Value],

    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"portalId", "dealId", "isDeleted", "associations", "properties", "imports", "stateChanges"}, {"Column1.portalId", "Column1.dealId", "Column1.isDeleted", "Column1.associations", "Column1.properties", "Column1.imports", "Column1.stateChanges"}),

    #"Expanded Column1.properties" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.properties", {"dealname", "hubspot_owner_id", "closedate", "dealstage", "go_live_date", "dealtype", "projected_start_date"}, {"Column1.properties.dealname", "Column1.properties.hubspot_owner_id", "Column1.properties.closedate", "Column1.properties.dealstage", "Column1.properties.go_live_date", "Column1.properties.dealtype", "Column1.properties.projected_start_date"}),

    #"Expanded Column1.properties.dealname" = Table.ExpandRecordColumn(#"Expanded Column1.properties", "Column1.properties.dealname", {"value"}, {"Column1.properties.dealname.value"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.properties.dealname",{"Column1.associations", "Column1.isDeleted", "Column1.portalId"}),

    #"Expanded Column1.properties.hubspot_owner_id" = Table.ExpandRecordColumn(#"Removed Columns", "Column1.properties.hubspot_owner_id", {"value"}, {"Column1.properties.hubspot_owner_id.value"}),

    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.properties.hubspot_owner_id",{{"Column1.properties.dealname.value", "Dealname"}, {"Column1.properties.hubspot_owner_id.value", "OwnerID"}}),

    #"Expanded Column1.properties.closedate" = Table.ExpandRecordColumn(#"Renamed Columns", "Column1.properties.closedate", {"value"}, {"Column1.properties.closedate.value"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1.properties.closedate",{{"Column1.properties.closedate.value", "Closedate Unix"}}),

    #"Expanded Column1.properties.dealstage" = Table.ExpandRecordColumn(#"Renamed Columns1", "Column1.properties.dealstage", {"value"}, {"Column1.properties.dealstage.value"}),

    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Column1.properties.dealstage",{{"Column1.properties.dealstage.value", "Deal Stage"}, {"Dealname", "Deal Name"}}),

    #"Expanded Column1.properties.go_live_date" = Table.ExpandRecordColumn(#"Renamed Columns2", "Column1.properties.go_live_date", {"value"}, {"Column1.properties.go_live_date.value"}),

    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Column1.properties.go_live_date",{{"Column1.properties.go_live_date.value", "Projected Golive Date"}}),

    #"Expanded Column1.properties.dealtype" = Table.ExpandRecordColumn(#"Renamed Columns3", "Column1.properties.dealtype", {"value"}, {"Column1.properties.dealtype.value"}),

    #"Renamed Columns4" = Table.RenameColumns(#"Expanded Column1.properties.dealtype",{{"Column1.properties.dealtype.value", "Deal Type"}}),

    #"Expanded Column1.properties.projected_start_date" = Table.ExpandRecordColumn(#"Renamed Columns4", "Column1.properties.projected_start_date", {"value"}, {"Column1.properties.projected_start_date.value"}),

    #"Renamed Columns5" = Table.RenameColumns(#"Expanded Column1.properties.projected_start_date",{{"Column1.properties.projected_start_date.value", "Projected Start Date"}}),

    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns5",{"Column1.imports", "Column1.stateChanges"})

in

    #"Removed Columns1"

[WendyGoh]

Hey @bdesilva,

When looking to page through the deals retrieved from this endpoint Get all deals, you would need to use both the offset and hasMore value. 

Over in this forum discussion - Pagination using Power BI - HubSpot Community there's a couple of working examples on how to implement the pagination for contacts and companies.

While there's isn't a specific example for deals, the mechanism of pagination is the same. In this case, I believe your team can work off through this example:

let
Pagination = List.Skip(List.Generate( () => [IsMore = null, Last_Key = 0, Counter = 0], // Start Value
each [IsMore] <> false,// Whilst this is true, keep going
each [WebCall = Json.Document(Web.Contents("https://api.hubapi.com/contacts/v1/lists/all/contacts/all?" & "hapikey=******" & "&property=firstname" & "&property=lastname" & "&property=createdate" & "&property=email" & "&propertyMode=value_and_history" & "&count=100" & "&vidOffset="&Text.From([Last_Key])&"", [Headers=[ContentType="application/json", Authorization="Bearer Token"]])),// retrieve results per call
Last_Key = try [WebCall][#"vid-offset"] otherwise 0,
IsMore = if [Counter] < 1 then null else [WebCall][#"has-more"],
Counter = [Counter]+1,
Table = Table.FromRecords(WebCall[contacts])
]
,each [Table] // selector
) ,1)

// in
// Pagination
,
Custom1 = Table.Combine(Pagination

which is shared by user @snamuth.

Hope this helps to shed some lights! 

[SprinkleData]

HI @bdesilva 

You can look into Sprinkle Data in order to grab details from Hubspot to Power BI or any other reporting tool. Alternatively you can use Sprinkle for reporting purpose as well without any extra cost. In short, Sprinkle is your one stop solution for data integration and reporting.

Regards

Sam

[bdesilva]

Hi Sam, looks interesting but am I just late for no free tier?