Integrating PipelineCRM Data with Power BI - An API-Based Approach

Recently, I noticed a growing need to integrate data from PipelineCRM into Power BI for advanced analytics. Surprisingly, I couldn’t find any comprehensive guide that detailed the step-by-step process. That’s why I’ve decided to create this post to bridge the gap.

In this guide, I will show you how to use PipelineCRM’s API to seamlessly connect your data with Power BI.

Step 1: Access the API Integration Section

To begin, navigate to PipelineCRM’s API integration page by using the following link:

https://app.pipelinecrm.com/admin/modern/api

1. Log in to your account.
2. Once logged in, click on Add Integration to create a new integration.

Step 2: Configure the Integration

When setting up the integration:

1. Fill in the details of your application.
2. Select Both JWT and API Key as the authentication strategy.

After completing the setup, ensure that the application is marked as active.

Step 3: Retrieve the API Key

1. Head over to the API Keys section.
2. Copy the API Key generated for your application and ensure that it’s enabled.

 

Step 4: Connect to PipelineCRM API from Power BI

Now that you have your API Key and App Key, it’s time to connect to PipelineCRM from Power BI:

1. Open Power BI Desktop.
2. Go to Home > Transform Data > Advanced Editor, and paste the following code:

let

    Source = Json.Document(Web.Contents("https://api.pipelinecrm.com/api/v3/deals.json?api_key=XXXX&app_key=XXXX")),

    #"Converted to Table" = Table.FromRecords({Source}),

    #"Expanded entries" = Table.ExpandListColumn(#"Converted to Table", "entries")

in

    #"Expanded entries"

1. Replace XXXX with your actual App Key and API Key.
2. Press Done to apply the query.

Step 5: Customize Your API URL

The example above retrieves deals data. You can modify the API URL to access other resources, such as contacts or companies. For details on all available endpoints, refer to the PipelineCRM API Documentation.

Step 6: Expand and Visualize Your Data

After successfully importing the data:

1. Expand the “entries” column in Power BI to view detailed information.
2. Use the imported data to create dashboards and visualizations that suit your needs.

That’s it! By following these steps, you can efficiently integrate PipelineCRM data with Power BI and unlock powerful insights for your organization.