cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xmrksthspt
Frequent Visitor

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?

1 ACCEPTED SOLUTION

@xmrksthspt,

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.
1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yuezhe-msft
Microsoft
Microsoft

@xmrksthspt,


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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

@xmrksthspt,

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.
1.PNG


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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Some records will list off their associations all in one box using semicolons, which makes data difficult to connect in Power BI.  For instance, the single ID's will match since it is a single reference but when a cell is filled in with multiple ID’s it will not.  How do I make this kind of data match up?

 

Deals

  

Contacts

 

Name

Associated Contacts

 

ID

Name

Deal 1

443059; 249950; 192529

 

443059

John Smith

Deal 2

443059

 

249950

Jane Doe

Deal 3

249255

 

192529

Ozzy Osbourne

Deal 4

249950; 420955

 

420955

Eddie Van Halen

   

249255

Slash

 

 

 

 

 

@xmrksthspt,

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.
1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ok that works.  The next problem is that when I run reports off of these records they show up as duplicates.  How do I make the reports deduplicate by the unique ID?

@xmrksthspt,

Please open a new thread describing the above issue and please share screenshot and sample data in the new thread.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors