Pipedrive to Power BI: illustrative current-snapshot example
Prepared 17 October 2026

All account keys, numeric IDs, titles, cursors, dates, probabilities and values
are synthetic teaching inputs. The JSON contains response-shaped subsets plus
separate teaching metadata, not a live API export or a complete API schema.
It contains no customer records, credentials, API request identifiers or contacts.

FILES AND LOCAL CHECK
pipedrive-source.json: seven synthetic deals, pipeline/stage lookups and page cursors.
deals-snapshot.csv: five current, non-archived, non-deleted reporting rows.
normalise-pipedrive.py: standard-library local normaliser and fixture checks.
measures.dax: seven measures for a table named PipedriveDeals.
expected-results.csv: independent numerical controls and explicit scope counts.

Extract the ZIP, then run from any directory with Python 3:
python3 /path/to/normalise-pipedrive.py --check
Without --check, the script regenerates deals-snapshot.csv beside itself.
It reads only the local fixture, makes no network calls and never changes a CRM.
--write-zip additionally rebuilds the deterministic download ZIP.

WHAT THIS SNAPSHOT MEANS
One synthetic account and one pipeline are captured at 2026-10-14T23:59:59Z.
The fact grain is one visible, non-archived, non-deleted deal at this snapshot.
All included values are GBP. There is no currency conversion. The normaliser
rejects mixed included currencies rather than adding them as if they were GBP.
The two source deal pages deliberately include a deleted row. A separate archived
endpoint-shaped page contains an archived won deal. Both are excluded from this
reporting scope, giving seven source rows and five staged rows.

Do not append repeated copies of this snapshot. The account key and deal ID are
unique within it; a repeated run replaces the CSV. A production current-state
store should upsert by account + deal ID and reconcile archive/deletion changes.
If you preserve multiple snapshots for history, add snapshot time to the grain
and filter to one snapshot before using these measures. Adding snapshot totals
would count the same opportunity repeatedly.

PROBABILITY AND CONTROLS
This example explicitly enables deal probability for its pipeline. A present
deal probability overrides stage probability; an explicit zero stays zero.
When deal probability is null, use stage probability. If neither is configured,
use 100%. Deal and stage probabilities are not multiplied together.
Open A: 10,000 x 20% stage fallback = 2,000.
Open B: 20,000 x 50% deal override = 10,000, even though its stage is 60%.
Open C: 5,000 x 0% deal override = 0, even though its stage is 60%.
Open count = 3; open value = GBP 35,000; weighted open value = GBP 12,000.
Won value = GBP 12,000; lost value = GBP 8,000.
Closed deals = 1 won + 1 lost = 2. Closed-deal win rate = 1 / 2 = 50%.

Weighted value is a probability-weighted planning number, not recognised revenue,
cash received or a promise of future sales. The win rate is the status mix of
closed deals in this visible current snapshot, not a historical cohort conversion
rate or the company's lifetime performance. Won value also describes deal status,
not accounting revenue. Archived/deleted/hidden deals change the reporting scope.

IMPORT TO POWER BI
Import deals-snapshot.csv as UTF-8 comma-separated text, with headers, and name
the table PipedriveDeals. Use whole numbers for IDs; decimal numbers for value and
probability columns; text for statuses/currency/account key. Blank probabilities
must stay null rather than zero. Parse snapshot_at, won_time, lost_time and
update_time as date/time/timezone with UTC; expected_close_date is date-only.
For a UTC date-only visual, derive its date after explicitly converting to UTC.
Import the seven DAX measures individually. Display monetary measures as GBP,
Open Deals/Closed Deals as whole numbers and Closed Deal Win Rate as a percentage.
Compare the controls before adding slicers, relationships or a date table.

The Python mapping and exact arithmetic have been executed locally. The DAX is
provided as a model definition for review. No credentialed Pipedrive request,
Power BI Desktop, DAX engine or Power BI Service refresh has been executed here.
This ZIP is a local teaching fixture, not a production connector or a PBIX file.

SOURCE TO STAGED FIELDS
deal_id <- deal.id; deal_title <- deal.title; status <- deal.status;
value/currency <- deal.value/deal.currency; owner_id <- deal.owner_id;
pipeline_id and stage_id join to the separate lookup IDs for names/probability.
deal_probability_pct <- deal.probability; stage_probability_pct <- stage.deal_probability.
effective_probability_pct/probability_source are derived with null-aware fallback.
snapshot_at/account_key come from teaching metadata, not returned deal fields.
Archived provenance comes from the separate archived-deals response group; no
invented is_archived field is presented as part of a Pipedrive deal response.
The source is a current-state subset. It does not supply stage-change history,
activities, custom-field definitions, product facts or deleted-record history.
Avoid one-to-many product/activity joins that multiply deal rows and value.

CURRENT CONNECTION NOTES AND PRIMARY SOURCES
Pipedrive's reference pages have /docs/api/v1/ in their URLs while documenting
selected v2 methods. Prefer /api/v2/deals, /pipelines, /stages and /dealFields
where available. Use additional_data.next_cursor until null, including lookup
pages. A failed/incomplete page must not be published as a complete snapshot.
Current docs specify x-api-token header for API-token integrations and OAuth for
apps. Store secrets outside public downloads and PBIX query text. OAuth deals:read
covers the relevant reads; authorisation and user visibility still bound coverage.
Deals lists omit archived deals; retrieve them separately if your scope includes
them. Deleted status is returned only when requested, for up to 30 days after
deletion. Build ongoing reconciliation rather than relying on a one-off export.
updated_since is inclusive and updated_until exclusive in RFC3339. Advance a
watermark only after every page succeeds. Stage movement requires changelogs or
snapshots captured over time; the current stage does not reconstruct the journey.

https://developers.pipedrive.com/docs/api/v1/Deals
https://developers.pipedrive.com/docs/api/v1/Pipelines
https://developers.pipedrive.com/docs/api/v1/Stages
https://developers.pipedrive.com/docs/api/v1/DealFields
https://pipedrive.readme.io/docs/core-api-concepts-pagination
https://pipedrive.readme.io/docs/core-api-concepts-authentication
https://pipedrive.readme.io/docs/marketplace-scopes-and-permissions-explanations
https://pipedrive.readme.io/docs/pipedrive-api-v2-migration-guide
https://support.pipedrive.com/en/article/probability-in-pipedrive

For scheduled reporting, first load into an access-controlled, supported source.
Keep extractor freshness separate from semantic-model refresh success. Microsoft
documents eight scheduled daily refreshes on shared capacity and up to 48 on
Premium/PPU/Fabric capacity; connection credentials/gateway requirements still
apply. RelativePath/Query can help some Web.Contents sources refresh, but do not
by themselves implement secure Pipedrive OAuth, pagination or token rotation.
https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data
https://learn.microsoft.com/en-us/powerquery-m/web-contents