Connect HubSpot CRM to Power BI via OData

Why connect HubSpot CRM to Power BI?

In Power Query, rename the OData table HubSpot_Deals to Deals before using the DAX measure below. HubSpotDeal is the backend model name, not the feed table name. Stage IDs and open/closed classifications can differ by portal; adapt the example filter to your pipeline.

HubSpot provides built-in dashboards, but growing organizations quickly reach limitations when sales data must be combined with financial ERP data (such as Exact Online or Twinfield) or project software (such as Exact Bouw or Robaws). Leadership and commercial managers need a consistent overview in Power BI of sales funnels, outstanding deals, pipeline value, and realized revenue.

Without an automated data connection, this leads to weekly manual spreadsheet exports. That costs time, creates version conflicts, and makes recurring executive reporting prone to errors. An OData connection via DigiData structures CRM data and makes tables directly available for Power BI.

How it works: connect once, then automatic

Calling the HubSpot API yourself from Power Query is complex and error-prone. DigiData handles it in two steps:

1. Connect once: You connect your HubSpot account through the official HubSpot sign-in. DigiData only asks for read access to contacts, companies and deals.
2. Automatic updates: DigiData retrieves your data in the background and prepares it for Power BI. Your reports refresh quickly, without using up your HubSpot limits.

Downstream OData feed and Power BI authentication

After synchronization, DigiData serves the data via a secure OData feed. In Power BI Desktop, configure the connection as follows:

1. Open Power BI Desktop, select Get Data, and choose OData feed.
2. Paste the unique DigiData feed URL from your dashboard.
3. For authentication, select Basic. Enter your DigiData OData API key in the Password field. The username can be any non-empty value (such as apikey), as DigiData validates the key in the password field and ignores the username.
4. Click Connect. The tables appear immediately in the Navigator.

Supported data entities and scope

DigiData's HubSpot synchronization focuses deliberately on the three central CRM core entities:

• Deals (HubSpotDeal): Includes deal name (Name), order amount (Amount), pipeline identifier (Pipeline), stage identifier (Stage), expected close date (CloseDateUtc), external ID (ExternalId), and timestamps.
• Companies (HubSpotCompany): Includes company name (Name), domain name (Domain), city (City), country (Country), sector (Industry), and phone number (Phone).
• Contacts (HubSpotContact): Includes first name, last name, email address, phone number, job title (JobTitle), and company name (Company).

[!NOTE] Important scope boundary: The feed delivers current snapshot records. There is no historical pipeline stage table, no automatic probability weighting, and no calculation of time-in-stage included in the data layer. You configure this analytical logic according to your needs in Power BI.

Data modeling, relationships, and reporting grouping

Because the source dataset does not supply a relational link table between deals and companies, model relationships in Power BI based on the available identifying attributes:

1. Stage and pipeline grouping: The fields Pipeline and Stage contain technical HubSpot IDs (such as pipeline = "default" and stage = "contractsent"). Create a simple reference dimension (Dim_Stage) in Power BI with columns for the technical Stage ID, display name (e.g. "Proposal Sent"), and a sort order (SortOrder 1 through 5). Connect this to the deals table for an intuitive funnel.
2. Time horizon and cohorts: Connect CloseDateUtc to a central date dimension (Calendar) to visualize quarterly forecasts and monthly conversion rates.

Worked practical example with explicitly fictional deal data

The following table provides a worked practical example showing how the deals table arrives structured in Power BI and how it can be aggregated. All mentioned company names, amounts, and dates are explicitly fictional and serve solely to illustrate calculations:

In Power BI, define concise, powerful DAX measures based on this table:

• Open Pipeline Value: Total Pipeline = CALCULATE(SUM(Deals[Amount]), Deals[Stage] IN {"qualifiedtobuy", "presentationscheduled", "contractsent"})
• Expected Quarterly Closing: filter on CloseDateUtc within the current reporting quarter.

Scheduled refresh in Power BI Service

After publishing the report to Power BI Service, configure scheduled refresh:

• Navigate to the semantic model settings and open Data source credentials.
• Select Basic authentication, enter your DigiData API key as the password, and set privacy level to Organizational.
• Microsoft documents in the official OData connector guide that Basic authentication in Power BI Service depends on host and gateway support. Refer to Microsoft Learn guidelines for credential troubleshooting.
• Schedule the refresh after DigiData completes its daily synchronization.

Want to see where your leads come from as well as the pipeline? Combine HubSpot with website data from Google Analytics 4 in Power BI, so traffic, conversions and won deals sit in one report.

Summary

By connecting HubSpot to Power BI via DigiData, you combine sales metrics directly with other business-critical reports. You work with current, structured tables without manual exports or fragile custom scripts. Learn more about the HubSpot integration or explore Power BI reporting.

You can also see pipeline, win rate and deals from HubSpot without Power BI in a sales dashboard.