Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.
We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.
You will also receive a complimentary subscription to TechRepublic's News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.
All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
How to connect Power BI to HubSpot
Your email has been sent
Learn how to access Hubspot data inside of Power BI to fluently analyze your company’s data.
People looking to gain insights from visual representations of their data can benefit from connecting their HubSpot data to Microsoft Power BI. Power BI is an advanced visualization tool that can transform data into visual insights for business intelligence.
HubSpot is a cloud-based CRM tool that can provide organizations with support for business growth through its sales, customer service and marketing software. The software enables users to access and analyze their data within the app to generate insights, but its reporting capabilities can be limited.
SEE: Hiring Kit: Database engineer (TechRepublic Premium)
By connecting the information from HubSpot and Power BI, you can transform your enterprise and CRM data to create insightful visuals and generate reports that can make it easier to understand and gain actionable insights from business data.
While the Power BI software connects to data sources to generate interactive reports from their data information, it does not provide direct native support for HubSpot integration. So how can you extract maximum value from your data?
After you have a HubSpot admin account and have created a Power BI account, you can follow the methods below to connect your systems for data access and increased functionality.
HubSpot APIs can be utilized to connect HubSpot CSVs to Power BI through a developer token-based authentication strategy.
To begin, go to the HubSpot admin panel. Here you can go to the settings icon on the navigation toolbar and use the integrations tab to generate an API key. If you’ve already generated an API key, click Show to display your API key (Figure A). Click Copy to copy your API key.
Now, you can use the following curl command to access the contact details from your HubSpot admin account:
curl -o contacts_details.json https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=<api_key>&count=10
Bear in mind that the API is paginated and has a limit of 100 items. Therefore, for a contact list containing over 100 items, you will be required to to use the vid-offset command to access the remaining information.
Once this is done, you will have a JSON file containing all of the customer contact information from your HubSpot account, named contacts_details.json.
To gain the relevant information from the JSON file and convert it into a CSV, you must use a command-line utility called ‘jq’
cat contacts_rdetails.json | jq -r ".contacts[] | [.vid,.properties.firstname.value,.properties.company.value,.properties.lastname.value] | @csv" >> contacts_data.csv
Now that you have the CSV file generated from these actions you can import this information into Power BI. In Power BI, hover over Get Data from the toolbar at the top of the screen to see the drop down menu. Select the Text/CSV option from the drop-down menu to import the CSV file (Figure B).
By following these steps you should be able to access all of the CSV file data from HubSpot within Power BI.
A no-code method to download CSVs from HubSpot is through the HubSpot user interface.
To access contact details, start in HubSpot and select Contacts, then click the drop-down button that says Export View (Figure C).
Select Export and choose CSV as the file format.
Once this is done, HubSpot will send an email to the registered account email address. This should contain a link to download the CSV file (Figure D).
From here you can import the CSV file information into Power BI by clicking Get Data from the toolbar at the top of the screen. Following Figure B once more, you can select the Text/CSV option to import the CSV file.
From here, you should be able to access all of the CSV file data from HubSpot within Power BI.
Another way to connect HubSpot to Power BI is to utilize a pre-built connector. These connectors work between applications to facilitate the integration process. Many are available online and carry out different processes to transport information between the HubSpot and Power BI systems
One example of a HubSpot-certified middleware solution would be the DataWarehouse.io HubSpot Power BI connector that can be accessed to connect HubSpot data to Power BI and is available in the HubSpot App Marketplace. This integration method can allow users to query and manipulate data within Power BI without affecting HubSpot database integrity.
First, download the HubSpot certified API app. Authorize the app to connect to your HubSpot portal by selecting Connect App in HubSpot desktop portal, and fill out the required information to activate the service.
To connect a new or existing Power BI document using the HubSpot Connector, you must first select the Get Data button from the toolbar at the top of the Power BI application screen.
Once you see the Get Data pop-up, select Database from the list of options on the left-hand side of the pop-up (Figure E) and click SQL Server Database from the list on the right-hand side of the pop-up.
Next, you will need to enter your login credentials, which were emailed to you when you signed up. Select OK (Figure F).
After this, you will need to enter your user information. First, make sure you have the Database option highlighted on the left side of the new pop-up (Figure G), then add your user information and select Connect.
Now you can populate all the tables with the information that is available from HubSpot. Select the tables you want to access in Power BI and click Load. This will load all of your fields with the data information from HubSpot. They will appear and become accessible under the section labeled Fields on the right side of the application (Figure H).
The Power BI Integration App supports connectors with the following HubSpot CRM and Sales features: Company records, contact activity, deals, meetings, tasks, ticketing, contact and company insights, custom reporting, goals and multiple deal pipelines.
Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
How to connect Power BI to HubSpot
Your email has been sent
Your message has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
The technologies could enable immersive experiences, accelerated AI automation and optimized technologist delivery in the next two to 10 years, according to the firm.
Are you an IT manager or executive trying to make the case for a new ERP vendor? Compare the top ERP software solutions with our list today.
Learn about the new features available with macOS 13 and how to download and install the latest version of Apple’s flagship operating system.
Get great deals on developer and Linux training courses, Microsoft Office licenses and more through these TechRepublic Academy offerings.
This comprehensive guide covers the use of services from multiple cloud vendors, including the benefits businesses gain and the challenges IT teams face when using multicloud.
Recruiting a Scrum Master with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit provides a customizable framework your business can use to find, recruit and ultimately hire the right person for the job. This hiring kit from TechRepublic Premium includes a job description, sample interview questions ...
Knowing the terminology associated with Web 3.0 is going to be vital to every IT administrator, developer, network engineer, manager and decision maker in business. This quick glossary will introduce and explain concepts and terms vital to understanding Web 3.0 and the technology that drives and supports it.
While the perfect color palette or the most sublime button shading or myriad of other design features play an important role in any product’s success, user interface design is not enough. Customer engagement and retention requires a strategic plan that attempts to measure, quantify and ultimately create a complete satisfying user experience on both an ...
IIoT software assists manufacturers and other industrial operations with configuring, managing and monitoring connected devices. A good IoT solution requires capabilities ranging from designing and delivering connected products to collecting and analyzing system data once in the field. Each IIoT use case has its own diverse set of requirements, but there are key capabilities and ...