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

Aryna's avatar
Aryna
Post Partisan
2 months ago

Hi, the oData Feed connector won't work here since HubSpot's API isn't oData. You would need to use the Web connector and pass your API key as a request header, then handle pagination and data transformations on top of that. For someone new to APIs it's a fairly steep learning curve, and HubSpot's data model adds another layer of complexity once you start trying to join contacts, companies and deals together.

Honestly for a reporting project like this it might be worth skipping the API route entirely and using a connector that's already built for this: https://vidi-corp.com/power-bi-hubspot-connector/

We used it to get our HubSpot data into Power BI without any API work as it loads everything into a cloud database, refreshes every 6 hours, and comes with a free template where the relationships between objects are already set up. Saved us a lot of back and forth. There's a free trial so worth testing with your own data before committing.