cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jigar1276
Helper I
Helper 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
    APIURL = "https://api.hubapi.com",
    MaxRecords = 100,
    Total =
    let
        Source = Json.Document(Web.Contents( APIURL & "/contacts/v1/contacts/statistics?hapikey=my-api-key")),
        Total = Source[contacts]
    in
        Total,
    OP= 
        let
            data =
                List.Generate(
                    () => [ a = 0, c = 0 ],
                    each [a] <= Total,
                    each [
                            a = [a] + MaxRecords,
                            c = [a]
                        ]
                    ),
                    op = Table.FromRecords(data)[a]
        in
            op,
    vidOffset=0,
    Loop = List.Transform( OP, each 
            let
                source = Json.Document(Web.Contents(APIURL, 
                    [
                        RelativePath = "/contacts/v1/lists/all/contacts/all?hapikey=my-api-key" & "&vidOffset=" & Number.ToText(vidOffset) & "&count=" & Number.ToText(MaxRecords)  & "&loop=" & Number.ToText(_)
                    ]
                ))
            in
                source
        ),
    #"Converted to Table" = Table.FromList(Loop, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"contacts", "has-more", "vid-offset"}, {"contacts", "has-more", "vid-offset"}),
    #"Expanded contacts" = Table.ExpandListColumn(#"Expanded Column1", "contacts"),
    #"Expanded contacts1" = Table.ExpandRecordColumn(#"Expanded contacts", "contacts", {"vid", "canonical-vid", "properties", "identity-profiles"}, {"vid", "canonical-vid", "properties", "identity-profiles"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded contacts1", "properties", {"firstname", "company", "lastname"}, {"firstname", "company", "lastname"}),
    #"Expanded firstname" = Table.ExpandRecordColumn(#"Expanded properties", "firstname", {"value"}, {"value"}),
    #"Expanded company" = Table.ExpandRecordColumn(#"Expanded firstname", "company", {"value"}, {"value.1"}),
    #"Expanded lastname" = Table.ExpandRecordColumn(#"Expanded company", "lastname", {"value"}, {"value.2"})
in
    #"Expanded lastname"

 

 

 

I have 37,500+ records in Hubspot Contacts.

This code is fetching first 100 records again and again. We are getting these two fields in each API call:

Jigar1276_0-1634195893101.png

 

"vid-offset" is some random number and not giving proper pagination.

 

The problem is with the next API calls I made, where we need to make next calls ony if "has-more" is true and we need to pass "vid-offset" value which we received in previous call as API parameter "vidOffset". 

 

Can anyone help me with editing my M query please?

 

For ref: I got this connector which is working perfectly but I do not want to use connector and gateway setup.

https://github.com/ranmax123/powerbi-custom-connectors/tree/master/HubSpot

 

Thanks in advance.

1 ACCEPTED SOLUTION
Jigar1276
Helper I
Helper I

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"

View solution in original post

4 REPLIES 4
Jigar1276
Helper I
Helper I

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"

View solution in original post

mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


V-pazhen-msft
Community Support
Community Support

@Jigar1276 

You can also take a look at the following document about the help functions about processing pagination:

Helper functions for M extensions for Power Query connectors | Microsoft Docs

 

Vpazhenmsft_0-1634538567001.png

 


Best Regards

Paul

Thanks a lot for reply @V-pazhen-msft ,

 

This looks like connectors, but I am looking for M query so I can get the data directly into power bi rather then using the gateway.

 

Also I have almost written following code which gives me the page by page data but problem is only that once all the pages are fetched it agains starting the fetching, so it seems never ending loop.

 

The API documentation is here: https://legacydocs.hubspot.com/docs/methods/deals/get-all-deals 

 

let
	APIURL = "https://api.hubapi.com/deals/v1/deal/paged?&limit=100&hapikey=my-api-key&offset=",
	
	Pagination = List.Skip(List.Generate( () => [Last_Key = "0", Counter=0],
		each [Last_Key] <> null,
		each [Last_Key = try if [Counter]=0 then "" else [Source][Value][offset] otherwise null,
		//each [Last_Key = try if Number.ToText([Source][Value][offset])="0" then null else [Source][Value][offset] otherwise null,
		Source = try if [Counter]=0 then
			Json.Document(Web.Contents(APIURL & Last_Key))
		else
		Json.Document(Web.Contents(APIURL & Number.ToText(Last_Key))),
		Counter = [Counter]+1
		],
		each [Source]
	),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
    #"Expanded Column1.Value" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.Value", {"deals", "hasMore", "offset"}, {"deals", "hasMore", "offset"})
in
    #"Expanded Column1.Value"

 

 

The output it gives is as follow:

Jigar1276_0-1634539710058.png

Required rows are only first 12 but it goes on and on repeating same 12 rows.

If you can please review and suggest to end the loop. Meanwhile I will go thru the link you sent.

 

Thanks a log.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors