Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

HubSpot Data Relationships?

 Hello, I have just gotten an ODBC connector to connect our HubSpot data to Power BI.  It is working just fine and I can see all of the tables, but I have no idea how the associations between tables work.  Does anyone have an example or mapping of how HubSpot tables are connected on the backend?

 

It is quite simple to connect Companies to Deals and Contacts since Contacts and Deals only have one associated Company record.  The confusing piece is that Contacts can have multiple deals connected and Owner records tie to multiple places in different tables.  When I try to set up the connections it says that they cannot be active.

 

For instance, I want to have all deals shown in one report module with another report module that shows all the contacts associated with the deals I have filtered for.  Is this even possible with the way HubSpot tables seem to be laid out?

  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous,

    You can split the Associated Contacts column in Power BI Desktop query editor, then create relationship using Associated Contacts column of Deals table and ID column of Contacts tables.


    Regards,
    Lydia

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous,


    Could you please post detailed error message here? There is a similar thread about how to handle relationship in Power BI for your reference.

    Besides, the reault you want to get can be calculated using DAX, please share sample data of your tables here and post your desired result based on sample data. Please review this post about how to share data.

    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I was just hoping someone here had used the HubSpot API to get data out in their table format so I could see how they had connected their data.  It is quite confusing how the tables work.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous,

        In Power BI Desktop, no matter which method you use to connect to the source, Power BI will automatically establish new relationships/autodetect existing relationships as long as you enable the relationship options.



        In your scenario, please share sample data of your tables and post expected result here.

        Regards,
        Lydia

  • Aryna's avatar
    Aryna
    Post Partisan
    1 month ago

    This is one of the trickiest parts of working with HubSpot in Power BI as many-to-many relationships between contacts and deals can't be handled with a direct active relationship, which is why you're hitting that error. You'd need a bridge table to resolve it properly.

    We ran into exactly this and ended up switching to a connector that has all of this pre-built: https://vidi-corp.com/hubspot-power-bi-connector/ 

    The associations between contacts, companies, deals and owners are already modelled correctly, so the cross-filtering between report pages you're describing just works out of the box. They offer a free Power BI template so you're not starting from scratch.