Help!! How do I connect Hubspot to Power bi without using 3rd party APIs/ connectors
I also have to make sure that latest data is shown in power bi after every schedule refresh.
The short answer is:
Unfortunately, getting a truly direct, no-code, and automatically refreshing connection from HubSpot to Power BI without using any intermediary service or custom code is not possible because HubSpot does not offer a native connector that Power BI can directly use for scheduled cloud refresh.
The only way to bypass third-party connectors is to use Power BI’s Web connector to call the HubSpot API endpoints yourself via a custom private app, which is a complex technical solution but it requires writing custom M-code in Power Query to handle pagination and data extraction, and you must correctly configure a Power BI Gateway to ensure the scheduled refresh works in the Power BI Service.
The long answer is:
The core problem is that Power BI’s scheduled refresh in the Power BI Service requires a static and secure data source.
HubSpot’s data is accessed through a REST API, which provides data in a JSON format and requires custom handling in Power Query (M-code) to retrieve all records across multiple pages (pagination).
While Power BI has a Web connector to pull this API data, configuring a reliable, refreshable data source that is not flagged as a dynamic data source error without a third-party tool is a complex technical task that must be done with custom M-code, specifically using the $Web.
Contents$ function with a static base URL, so that the Power BI Service will accept the data source for scheduled refresh.
This method requires a deep understanding of M-code and the HubSpot API structure, including how to securely pass the private app token and iterate through all the data pages, and it may still require an On-premises data gateway for Power BI to maintain the connection and refresh schedule.
If the technical challenge of writing custom M-code for the HubSpot API is too much, which it often is, your goal of avoiding third-party connectors and having scheduled refresh is nearly impossible without using some type of intermediary tool or platform.
This is where an alternative method using HubSpot API, Power BI REST APIs, Google Tag Manager, and a server-side tagging environment like Stape or Google Cloud Platform becomes an excellent and cheap solution, particularly for new data being generated, like form submissions or events.
This approach is cheap because it leverages tools that either have a free tier or minimal usage cost and it is incredibly powerful for tracking specific user actions.
Instead of pulling all historical data from the HubSpot API into Power BI, you can push key real-time events from your website directly into a Power BI Push dataset via its REST API.
First, you set up a server-side tagging environment, like a cloud function on Google Cloud Platform or a server on Stape, which acts as the middleware you initially wanted to avoid, but is under your full control and not a commercial data connector.
Second, you use Google Tag Manager on your website to intercept high-value events, such as a contact filling out a form or reaching a certain stage – these are your custom or standard events like generate_leadpurchase.
Third, instead of sending these events to HubSpot, you send them to your server-side environment.
Finally, the server-side code takes this event data and uses the Power BI REST API to instantly push a new row into a designated Power BI Push dataset.
Because Push datasets in Power BI are designed for real-time streaming, they are refreshed instantly as data is pushed, which completely eliminates the need for a manual or scheduled refresh and provides real-time data to your Power BI report.
While this method does not help with historical HubSpot data, it is a very inexpensive and robust way to stream fresh event data directly into Power BI, giving you the fastest possible refresh rate for new activity.
About The Author
