Forum Discussion
Jigar1276
4 years agoHelper I
Get data from Hubspot by making API calls (Without using connectors)
Hi, I want to use this Hubspot API to get data into the Power BI: https://legacydocs.hubspot.com/docs/methods/contacts/get_contacts I am using following M Query to do that: let
API...
- 4 years ago
Got the solution. The code to get all the deals is follow:
let apiUrl = "https://api.hubapi.com", dealsProperties = "hs_forecast_amount,hs_manual_forecast_category,hs_forecast_probability,amount,amount_in_home_currency,closedate,createdate,dealname,dealstage,dealtype,pipeline,hubspot_owner_id,num_notes,num_contacted_notes,closed_lost_reason,closed_won_reason", propertiesQString = "&properties=" & Text.Replace(dealsProperties, ",", "&properties="), suffixUrl = "/deals/v1/deal/paged?hapikey=my-api-key-here&limit=250&includeAssociations=true" & propertiesQString & "&offset=", Source = let jobsJsonPaginated = List.Generate( () => [pageResult = null, nextOffset = 0, counter = 1], each [counter] <= 1 or [nextOffset] <> 0, each [pageResult = try let response = Json.Document(Web.Contents(apiUrl, [RelativePath = suffixUrl & Text.From([nextOffset])])) in response otherwise null, nextOffset = try pageResult[offset] otherwise 0, counter = [counter] + 1], each [pageResult]), jobsJsonPaginated2 = List.Skip(jobsJsonPaginated, 1) in jobsJsonPaginated2, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
mahoneypat
4 years agoMicrosoft Employee
Looks like your API has an offset parameter. Please see this video for a different/simpler way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat