Forum Discussion

JordanPearson's avatar
JordanPearson
Helper II
4 years ago
Solved

Pull data from Hubspot into PBI via API

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 co...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Ray_Brosius's avatar
Ray_Brosius
Helper III
2 years ago

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's avatar
Jmek
New Member
2 years ago

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's avatar
    Ray_Brosius
    Helper III
    2 years ago

    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's avatar
      Jmek
      New Member
      2 years ago

      Thank you for the info, much appreciated!