1. Introduction

In this tutorial, we’ll walk you through the process of setting up a custom connector to extract data from HubSpot and create a Power BI report. This custom connector can be found on our GitHub site (https://github.com/audoxcl/Power-BI-HubSpot-Connector).

2. Setting Up the Connector

Start by visiting our GitHub site where you can find the custom connector. In the video description, you’ll find a link to detailed instructions on how to set up this connector.

3. Preparing the Power BI Report

Before we dive into the connector setup, ensure you have Power BI Desktop installed on your system. If you don’t have it already, you can download and install it from the official Power BI website.

4. Accessing the Power Query Editor

1. Open Power BI Desktop and create a new report. For this tutorial, we’re going to use the Power BI report that’s inside our Github site.
2. To configure the custom connector, click on “Transform Data” to open the Power Query Editor.

3. In the Power Query Editor, you will find the necessary elements for configuring the connector.

5. Connector Configuration

1. Within the Power Query Editor, configure the custom connector by providing the following information:
   • Connector URL: This is the URL where the connector is installed.

• Connector Token: Note that you will require two tokens:
  1. one for the connector itself 
  2. another to access HubSpot. 

You can name them as per your preference. Detailed instructions on obtaining these tokens can be found in the video description.

2. Set the Record Limit to define the number of records to fetch at a time. For example, you can fetch them ten at a time.

3. The Account ID is crucial for generating HubSpot URLs. Ensure you have the correct account ID to link to your HubSpot records.

You can find your Hubspot’s account id in the url generated every time you log in your Hubspot account.

6. Building the Query

1. Let’s focus on how to construct the query for the “deals” table as an example. Right click on the selected table and let’s click on the Advanced Editor.

2. In the current view, we can see how to bring in data in JSON format from the connector’s URL.

3. Use the “Action Get Records” method from the source code to extract the “deals” object. Pass the HubSpot token as an argument and specify the limit.

4. Pass the necessary properties to obtain from each deal.

5. Include “companies” in the “Association” variable to extract the company ID for each deal.

6. In the “Headers,” make sure to pass the connector token.

7. Data Transformation

The subsequent steps are designed to transform the JSON data into the essential fields required for your report.

8. Building your own Power BI Report

1. You can now return to Power BI Desktop from the Power Query Editor.
2. Create visuals, charts, and tables based on the data you’ve extracted from HubSpot.
3. Customize the report as desired, including filters and user-specific information.

9. Testing the Data

1. Now, it’s time to make an adjustment to the opportunity amount. In this particular instance, we will focus on the deal associated with Smallville Resources. To do this, simply select the icon that links the URL for the deal in HubSpot to the Power BI report.

2. In the HubSpot deal, the next step involves clicking on the Edit icon. Subsequently, we’ll adjust the Deal amount to $30,000, and then ensure to save the changes. With these modifications in place, it’s time to return to our Power BI report.

3. Now we can Refresh the data for the Power BI report.

4. Finally, we can see that the amount of the selected Deal has changed to $30K.

10. Conclusion

In summary, this comprehensive tutorial has guided you through the process of seamlessly connecting HubSpot and Power BI to craft a data-centric Power BI report. With the establishment of this custom connector, you gain the capability to effortlessly extract data from your HubSpot CRM and seamlessly integrate it into your reports. Consequently, you can maintain the currency of your reports with real-time data, facilitating informed business decisions based on the most current information available.

By diligently following the comprehensive steps outlined in this tutorial, you’ve acquired valuable skills to:

• Configure the custom connector, ensuring secure and efficient data transfer.
• Create queries to fetch specific data from HubSpot.
• Transform raw JSON data into fields suitable for your reports.
• Build visually appealing and informative Power BI reports.
• Test and refresh your data to keep your reports current.

Step 11: Additional Resources

For further assistance and resources, we provide the following links:

1. Custom Connector on GitHub: You can find the custom connector used in this tutorial on our GitHub site. Access it here.
2. Sample Power BI Report: We’ve also made available a sample Power BI report for Power BI Desktop. You can download this report here. It can serve as a solid foundation for your own reports and help you understand how to visualize your HubSpot data effectively.

Contact Us

If you’d like to get in touch with Audox, you can easily reach out through their official contact page here. Whether you have questions or inquiries, or you’re interested in our services. Audox is committed to providing excellent support and is just a click away from addressing your needs.