Connect Pipedrive to Power BI: API & Example

Bring your Pipedrive pipeline into Power BI with deal totals you can compare with the selected CRM view. Start with a defined sales question, authorised access and the records that belong in the report. Then map the current deals, pipelines and stages before adding measures or a refresh schedule.

The synthetic sales pipeline example below turns response-shaped JSON into five reporting rows. It shows £35,000 open pipeline and £12,000 probability-weighted open pipeline, with the rule behind each amount. The local transformation and arithmetic are checked; authenticated extraction, DAX execution and Power BI Desktop or Service testing are separate production steps.

Choose a Pipedrive connection route

Extraction and storage can form parts of the same design. A warehouse is useful when shared transformations or retained history justify it; it is not a prerequisite for every focused report. Our data integration service covers that architecture decision.

Microsoft's Power Query connector directory does not list a dedicated Pipedrive source. The Microsoft Pipedrive connector is documented for Power Automate, Logic Apps and related products. Check the host product before treating a connector listing as a Power BI connection.

Extract deals and their lookup tables

Pipedrive's Deals reference documents GET /api/v2/deals. Select the statuses and account scope required, follow additional_data.next_cursor until the list is complete, and keep an extraction timestamp. The current list defaults to non-archived deals; retrieve archived deals separately when the agreed report includes them.

For a live extraction, use the matching Pipelines, Stages and Deal fields endpoints. Their documentation URLs include v1 while the selected current methods use v2. Join on IDs, retain the lookup names and confirm each stage belongs to the expected pipeline.

The v2 migration notes describe changed fields: owner references are IDs, custom fields sit inside custom_fields, and weighted_value is removed. Map the actual response rather than copying an old v1 expansion or relying on a stage name as a stable identifier.

Pipedrive's authentication instructions specify the x-api-token header for API-token integrations and OAuth for apps. Keep credentials in the extraction service or suitable connector, outside shared report files and public examples. For an OAuth reporting app, read scopes such as deals:read cover the relevant entities; the authorising user's visibility still determines which records can be returned. Activities and change-history routes have their own scope requirements.

Download and run the snapshot example

Download the complete example ZIP, or inspect the source JSON, normaliser, reporting CSV, DAX measures, expected results and README. The files are ungated.

The teaching fixture uses one invented account, one pipeline and GBP values at 14 October 2026, 23:59:59 UTC. The JSON contains response-shaped subsets with separate teaching metadata. Every identifier, cursor, date and amount is invented.

Extract the ZIP, read the script and run with Python 3:

python3 normalise-pipedrive.py --check

The standard-library script checks the local source mapping and expected controls. Without --check, it rewrites deals-snapshot.csv. It makes no network calls. The seven source deals become five current, non-archived, non-deleted deals in the reporting table; one archived won deal and one deleted deal are explicitly outside this example's scope.

Model one current row per deal

The fact grain is one account + deal at one snapshot. Join pipeline and stage lookups before writing the flat CSV. Retain the original probability, its stage fallback, the effective percentage and the rule selected. Keep activities and products in separate facts or aggregate them before joining, so a deal's amount is counted once.

The example enables deal-level probability on its pipeline. Pipedrive's probability documentation gives priority to a configured deal probability. A null value falls back to the stage probability, with the documented default applied when neither is configured. An explicit 0% remains zero. Deal and stage probabilities are not multiplied.

Weighted pipeline is a probability-weighted planning measure. It is not recognised revenue, cash received or a promise that those deals will close.

Import into Power BI and add measures

1. Choose Get data → Text/CSV, select deals-snapshot.csv and open Transform Data.
2. Name the table PipedriveDeals. Use Whole Number for the sample IDs, Fixed Decimal Number for value and Decimal Number for probability fields. Keep missing probabilities null. Use Date for expected_close_date and Date/Time/Timezone for the UTC timestamps before deriving any report dates.
3. Keep statuses, currency, account key, labels and probability_source as Text. Choose Close & Apply.
4. Add each definition from measures.dax as a separate measure. Format values as GBP, counts as whole numbers and the win rate as a percentage.
5. Create headline cards and a table by deal, then compare the controls before adding date slicers or relationships.

Open Deals =
CALCULATE ( COUNTROWS ( PipedriveDeals ), PipedriveDeals[status] = "open" )

Open Pipeline Value =
CALCULATE ( SUM ( PipedriveDeals[value] ), PipedriveDeals[status] = "open" )

Weighted Open Pipeline =
SUMX (
    FILTER ( PipedriveDeals, PipedriveDeals[status] = "open" ),
    PipedriveDeals[value] * DIVIDE ( PipedriveDeals[effective_probability_pct], 100 )
)

The download also defines won value, lost value, closed-deal count and closed-deal win rate. Use a date dimension only after deciding which date answers the question. Expected close date, won time and lost time represent different events.

Reconcile the selected CRM view

For this fixture, compare 3 open deals, £35,000 open value and £12,000 weighted open value. The remaining included deals are one won deal worth £12,000 and one lost deal worth £8,000. Check the individual deal IDs, status filters, currency and probability rule as well as the totals.

Current closed-deal win rate = 1 won ÷ (1 won + 1 lost) = 50%

This is the current status mix of the included closed deals, not a historical cohort's conversion rate. Won deal value also describes CRM status, rather than an accounting revenue measure. If the business needs a monthly win rate, agree its date basis and treatment of reopened, archived, deleted and hidden deals first.

Run the local normaliser again. The same input should produce the same five rows. A current-state production table should upsert by account + deal ID, rather than append repeated loads. If snapshots are retained for trends, include the snapshot in the grain and make a current-pipeline report select one snapshot.

Retain history and schedule updates deliberately

A deal's current stage does not reconstruct its journey. Stage velocity and conversion require captured history with known coverage. Pipedrive documents changelog and flow endpoints separately in the Deals reference. Check their permissions and returned history before designing a stage-duration report. Regular snapshots provide observations from the point capture begins; they do not recover every movement between observations.

For incremental extraction, the Deals reference supports updated_since inclusively and updated_until exclusively. Advance the saved watermark after every required page succeeds, retain an overlap where needed, and upsert repeated records. Reconcile archive/deletion changes separately. Deleted deals are returned only when explicitly requested and within the documented retention window, so an old missed deletion cannot be assumed recoverable from the current list.

Follow Pipedrive's rate-limit and budget rules, handle failed pages and publish a complete validated source snapshot before refreshing Power BI. Configure the published source's credentials and any gateway, then check source time, row counts, control totals and Service refresh history. Microsoft's refresh documentation describes capacity and connection dependencies; our gateway guide covers the setup checks.

A successful local CSV import does not verify a live schedule. For a wider CRM model, the HubSpot connection guide covers another source, while the professional services dashboard explains pipeline alongside delivery reporting.

Discuss your Pipedrive reporting

Tell us about your Pipedrive reporting: accounts, pipelines, currencies, reporting questions, history and refresh schedule. The link opens an editable integration enquiry. See our published BI pricing for delivery scope and bands.

Technical sources checked on 17 October 2026. Confirm the current endpoints, account permissions and reporting definitions when scoping a live build.