Fetched data from Hubspot to power bi using api (Without connector)

[Puja_Kumari25]

Hi all, 

I am trying to fetch data from HubSpot to Power Bi using the API, but I am not able to generate the API key, and the account setting option is not visible in my HubSpot UI.

[CoderZen08]

Hi @Puja_Kumari25 As a workaround, maybe you can use a 3rd party connector, it can save you a lot of time compared to other options. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Hubspot connector in the data sources list:

After that, just grant access to your Hubspot account using your credentials, then on preview and destination page you will see a preview of your Hubspot fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous · Accepted solution]

Hi @Puja_Kumari25 ,

I think you can refer to this solved post with simmilar issues like yours.

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"

You may also refer to this document to learn more deatils: How to Connect HubSpot to Power BI For Data Automation | Coupler.io Blog

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.