Pull data from Hubspot into PBI via API

[JordanPearson]

Hi all, 

We have an existing project to improve our reporting capabilities around the data we hold in Hubspot (CRM platform). 

The biggest problem we face right now is that we don't have a connector between Hubspot & Power BI, meaning we can't currently connect PBI and Hubspot together.

After having some discussions interally & with Hubspot, it's been advised that Hubspot expose all of their data via API which we're advised we should be able to connect to.

The method I was thinking was: 

* Get API key from Hubspot

* Get API URL from Hubspot

PBI: Getdata > oData Feed > Advanced > URL > API key > Data pulls through.

However; when trying this I receive an error message "A web API key can only be specified when a web API key name is provided". 

I'm completely new to the world of API's - is the theory above correct for what I'm trying to do? What does it mean by "API key name", is this another way of saying API URL?

Any general advice / articles / examples would be incredibly appreciated, and please let me know if you need any further info!

[Cayshin]

Here's how I'm getting the data into a query that is compatible with Power BI Online's scheduled refresh (i.e. it doesn't compain about "dynamic datasources"). Code is based on this other solution.

Here are some examples below. 

Get all Marketing Emails

let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "marketing-emails/v1/emails", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[objects],
Next = [limit="1000", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,

Fullset = GetPages([limit="1000"]),

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

in
#"Converted to Table"

Get all CLICK events

let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "email/public/v1/events", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[events],
Next = [limit="1000", eventType = "CLICK", offset = Source[#"offset"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,

Fullset = GetPages([limit="1000", eventType = "CLICK"]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

#"Converted to Table"

Get all Contacts

let
GetPages = (queryParams)=>
let
Host = "https://api.hubapi.com",
Source = Json.Document(
Web.Contents(
Host,
[RelativePath = "crm/v3/objects/contacts", Query = queryParams, Headers=[#"Content-Type"="application/json", Authorization="Bearer INSERT-YOUR-TOKEN-HERE"]]
)),
LL= @Source[results],
Next = [limit="100", properties = {"hubspot_owner_id", "firstname" , "lastname", "company" , "email"}, after = Source[#"paging"][#"next"][#"after"]],
result = try @LL & @GetPages(Next) otherwise @LL
in
result,

Fullset = GetPages([limit="100", properties = {"hubspot_owner_id", "firstname" , "lastname", "company" , "email"}]),
#"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Converted to Table"

I'm not hitting the API rate limit, so I don't need to worry about it, but if you are then refer to Ray_Brosius's answer above on using Function.InvokeAfter().

[user_k]

Hello @Cayshin - does the above strategy still work for you?
I'm trying to use the "Get all CLICK events" code in my Power bi report (via a new blank query, and adding the code + my Hubspot access token to the advanced query editor), but each time I try I get stuck on the "Edit Credentials" piece. I try using the Web Api credential option by adding my access key again, but get this error:

Do you have any advice? 

[Cayshin]

Yes, it still works.

If you get the "Edit Credentials" prompt, you'll want to select the Anonymous option.

[user_k]

Thank you @Cayshin  - that does seem to work!

I just need to figure why I get "Expression.Error: Evaluation resulted in a stack overflow and cannot continue." even when I reduce the limit from 1000 to 10. 

[Brianyoung]

You can leverage Skyvia to integrate Hubspot with Power BI in two main ways. First, you can replicate your Hubspot data to a data warehouse and subsequently connect it to Power BI. Alternatively, you can expose your Hubspot data as an OData endpoint that Power BI can directly consume. Both methods facilitate seamless data sharing and analytics.

[ReportBuilder07]

Hi @JordanPearson I know this reply is very late lol. As a workaround, maybe you can use a 3rd party connector, which also pulls the data directly from the API, but it a lot more user friendly. 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. 

[Ray_Brosius]

@JordanPearson ,

We moved to use Snowflake and as such there is a direct datasynch from Hubspot to out Snowflake instance.  Hubspot uses snowflake as it's backend and they use a synch technology which you have to sign up for from HS and pay, but it eliminates any extraction from HS.   We then can write queries against the Snowflake instance to get to any data.

[Ray_Brosius]

We pull data from Hubspot regularly via Power Query.  You have to be careful withe Rate Limitations and Throttling from HS.

The code at the end gets the "tickets" object from Hubspot and the associations ..

You can then simply expand the Properties to get the properties that you need.

You should process the associatoins seperately.  

I get this data and then use as source to process the Properties in one table and Associations in another.

** This part of the code is setup to "slow down the extraction" so as to NOT hit the HS rate and throttling limitations.  the duration is set to a half a second per page of results

** The properties="TicketProperties" is a parameter where I create a list of the Ticket Properties to be returned

)) otherwise null, #duration(0,0,0,.5)),

let
jobsJsonPaginated = List.Generate( () =>
[pageResult = "x", nextOffset = "0",counter = 1],
each [pageResult] <> null,
each [pageResult = Function.InvokeAfter(()=> try Json.Document(Web.Contents("https://api.hubapi.com/crm/v3/objects/tickets",
[
Headers = [Authorization = "Bearer YOUR API KEY" ],
Query=
[
limit=defaultMaxRows,
properties = TicketProperties,
associations="Companies",
includeAssociations="true",
after=Text.From([nextOffset])
]
]
)) otherwise null, #duration(0,0,0,.5)),
current_results = pageResult[results],
paging_section = pageResult[paging],
offset = paging_section[next],
nextOffset = try offset[after] otherwise null

],each [current_results]),
jobsJsonPaginated2 = List.Skip(jobsJsonPaginated, 1),
#"Converted to Table" = Table.FromList(jobsJsonPaginated2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "properties", "associations", "createdAt", "updatedAt", "archived"}, {"id", "properties", "associations", "createdAt", "updatedAt", "archived"})
in
#"Expanded Column2"

[Jmek]

Hi Ray, were you able to set this up from the private apps? We used to use Salesforce and have now just moved to Hubspot. With Salesforce I had just pulled the API into PowerBI and was able to do all of my dashboarding. Now moving into Hubspot I see the API has been discontinued in 2023. Could you please share the best way for me to pull data into PowerBI for Dashboarding?

[Ray_Brosius]

We were able to use the private app.. just needed to use the API key for the private app.    As I stated in a reply to the general thread though.  We have moved to use Snowflake as our "Data Warehouse/lake" solution. The main driver being that HS has a direct synch with Snowflake.  If you have snowflake and you are setup to use/pay for the Data Synch then all the extraction issues with HS go away.  You still have to then extract from the Snowflake Tables and deal with the HS data model.  but you are not going to hit rate limits and such and you can use Snowflake to aggregate where needed or use SQL to get Transform data as necessary.     A much better solution.. 

[Jmek]

Thank you for the info, much appreciated!

[BayardBradford]

A HubSpot Certified App is available in the HubSpot App Marketplace for connecting HubSpot to Power BI.  A free trial is available.

https://ecosystem.hubspot.com/marketplace/apps/marketing/analytics-data/powerbi

Here is a map of data flow from HubSpot to Power BI that the integration provides:

HubSpot Data           Data Flow      Power BI

Companies	-->	Companies
Contacts	-->	Contacts
Deals	-->	Deals
Engagements	-->	Engagements
Email Events	-->	Email Events
Tickets	-->	Tickets
Owners	-->	Owners
Products	-->	Products
Forms	-->	Forms
Companies	-->	Company Properties
Contacts	-->	Contact Properties
Associations	-->	CRM Associations
Deal Pipelines	-->	Deal Pipelines
Deals	-->	Deal Properties
CampaignEmailEvents	-->	Marketing Email
Custom Objects	-->	CRM Associations
Quotes	-->	CRM Associations
Contact Lists	-->	Contact Lists
Website Pages	-->	Analytics
Workflows	-->	Workflows

Here is a link to the documentation: https://support.datawarehouse.io/hc/en-us/articles/360006051874-Power-Bi-Connector-Manual

[janunez]

Hi @JordanPearson !

You can try our open source connector:

https://www.youtube.com/watch?v=oOmJJA8_kWg

Javier Núñez, CEO

Audox

CRM, BI, PM Consulting Services

www.audox.com

[JamesJenava]

Your approach is mostly correct. The "API key name" is typically a header or parameter name required for authentication. For easier integration of HubSpot data into Power BI, consider using Skyvia. It provides a no-code solution to connect HubSpot with Power BI, bypassing the complexities of direct API integration.

[Anonymous · Accepted solution]

HI @JordanPearson,

According to the error message, it seems like you directly input API key value into the data connector, right? AFAIK, this connector will require you to define what type of API key that sends to the API service. (normally they will be defined as 'key name' = 'key value' to use in the connector)

Here is the sample query:

let
    Source =
        OData.Feed(
            "<API URL>",
            null,
            [Headers = [
                #"ApiKey" = "<YOUR API KEY>"
            ]]
        )
in
    Source 

Notice: #"ApiKey" part can be changed, you can check the API document definition first.

Regards,

Xiaoxin Sheng