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!

[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

[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.

[v-shex-msft · 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