Power BI developer on retainer: vw_InvoiceDetails DISTINCT removed from SQL Server view — FACT_INVOICES loads 1,284,293 rows instead of 847,293 — all revenue measures inflated 1.515× for 37 days

Power BI Pro • Import mode • SQL Server 2019 • On-Premises Data Gateway 3000.164.8 • Power Query M • SELECT DISTINCT • payment-split duplicates • SUM measure inflation • retainer billing

Bridgewater Professional Services Group had been watching its revenue dashboard for five weeks without concern. The September 2026 revenue figure sat at $9,695,273 — up from August’s $8.9 million, which the finance team attributed to a strong pipeline close. On October 21, 2026, the CFO ran a routine cross-check against the accounts receivable ledger before the monthly board report and found a 51.8% discrepancy: the AR ledger showed September revenue of $6,384,293. The Power BI developer retainer was engaged the same afternoon. Within 90 minutes, the root cause was identified: a SQL Server DBA had removed a SELECT DISTINCT clause from the source view that Power BI’s Import dataset used as its primary invoice fact table. Every invoice with more than one payment split now appeared two or three times in the Power BI dataset. Every SUM([InvoiceAmount]) measure in every report had been inflated by a factor of 1.515 for 37 consecutive daily scheduled refreshes — and Power BI had reported each of those refreshes as a success.

Bridgewater Professional Services Group ran Power BI Pro with a shared Power BI Service workspace named “BPSG Analytics.” The central financial dataset, BPSG_Revenue_Analytics.pbix, operated in Import mode. Its primary fact table, FACT_INVOICES, was loaded via a Power Query M step that read from a SQL Server 2019 on-premises database through an On-Premises Data Gateway (version 3000.164.8 on BPSG-GATEWAY-01). The M query was: Source = Sql.Database("BPSG-SQL-01", "BPSG_Finance"), dbo_vw_InvoiceDetails = Source{[Schema="dbo",Item="vw_InvoiceDetails"]}[Data]. The source view dbo.vw_InvoiceDetails had been built in 2024 to join the normalized invoice tables (INVOICES, INVOICE_LINE_ITEMS, PAYMENT_SPLITS) into a flat row per invoice per payment split. The view’s SELECT DISTINCT clause had been added during initial development to collapse invoices with multiple payment splits into a single row by projecting only invoice-level columns (not split-level columns). The result was one row per invoice: 847,293 rows as of September 13, 2026.

On September 14, 2026, a SQL Server DBA on the database team ran a performance review of the 20 heaviest views on BPSG-SQL-01. The dbo.vw_InvoiceDetails view appeared on the list because its execution plan showed a Sort operator for the SELECT DISTINCT deduplication step. The DBA examined the view’s join structure, checked PAYMENT_SPLITS for any rows, and found that the PAYMENT_SPLITS table had been populated with split records for approximately 437,000 invoices that had been processed through the firm’s payment plan feature (introduced in early 2026). The DBA concluded that DISTINCT was “probably just a development artifact” and removed it, then ran EXEC sp_refreshview 'dbo.vw_InvoiceDetails' to update the view metadata. Row count for the view after the change: 1,284,293 rows. No Power BI developer was notified.

How Power BI Import mode datasets load SQL Server view changes without validation

When a Power BI Import dataset runs its scheduled refresh, Power BI executes the M query against the data source and loads the resulting rows into the in-memory dataset. The refresh engine does not compare the incoming row count against the prior refresh’s row count, nor does it check whether the data distribution of key measure columns has changed significantly. A view that returns 847,293 rows in one refresh and 1,284,293 rows in the next refresh is treated as a normal increase — no alert is generated, no warning appears in the Power BI Service refresh history, and the dataset’s last refresh timestamp is updated to reflect the successful completion.

The Power BI Service dataset refresh history for BPSG_Revenue_Analytics showed Refreshed status for all 37 daily refreshes from September 14 through October 21, 2026. The row count increase from 847,293 to 1,284,293 (a 51.5% increase over six weeks) was indistinguishable from organic business growth in the Power BI monitoring interface. The dataset size in MB increased proportionally, but no size-based alert had been configured.

-- SQL Server 2019 BPSG-SQL-01, dbo.vw_InvoiceDetails
-- BEFORE change (September 13 2026): SELECT DISTINCT present
-- Row count: 847,293

CREATE VIEW dbo.vw_InvoiceDetails AS
SELECT DISTINCT
    i.InvoiceID,
    i.ClientID,
    i.InvoiceDate,
    i.InvoiceAmount,
    i.ServiceLine,
    i.SalesRepID,
    i.InvoiceStatus,
    i.DueDate,
    i.PaidDate
FROM dbo.INVOICES i
JOIN dbo.INVOICE_LINE_ITEMS li  ON li.InvoiceID = i.InvoiceID
LEFT JOIN dbo.PAYMENT_SPLITS ps ON ps.InvoiceID = i.InvoiceID;

-- AFTER change (September 14 2026): SELECT DISTINCT removed
-- Row count: 1,284,293  (+437,000 payment-split duplicate rows)
-- Invoice with 1 payment split:  1 row (no change)
-- Invoice with 2 payment splits: 2 rows (was 1)
-- Invoice with 3 payment splits: 3 rows (was 1)

-- Distribution of PAYMENT_SPLITS rows per InvoiceID:
SELECT split_count, COUNT(*) AS invoice_count
FROM (SELECT InvoiceID, COUNT(*) AS split_count FROM dbo.PAYMENT_SPLITS GROUP BY InvoiceID) t
GROUP BY split_count ORDER BY split_count;
-- split_count  invoice_count
-- 2            389,847
-- 3             47,153
-- (total: 437,000 invoices with payment splits)

The inflation was consistent across the entire dataset. Every SUM([InvoiceAmount]) measure in the Power BI model returned 1.515 times the correct value, because the average invoice appeared 1.515 times in the dataset (410,293 single-row invoices at 1× and 437,000 multi-split invoices at 2× or 3× average 2.108× = blended 1.515×). The “September 2026 Revenue” KPI on the executive dashboard showed $9,695,273. The actual September invoiced revenue, per the AR ledger, was $6,384,293. The inflation of $3,310,980 (51.8%) was large enough to matter but small enough that none of the report viewers independently cross-checked against another source for 37 days.

Diagnosis: Power BI dataset row count, source view inspection, and SQL Server PAYMENT_SPLITS audit

-- Diagnosis Step 1: Check current FACT_INVOICES row count in Power BI dataset
-- Power BI Desktop → Data view → FACT_INVOICES → row count
-- Current: 1,284,293 rows
-- Prior (from September 13 refresh — checked in Power BI activity log via REST API):
--   September 13 refresh row count: 847,293 rows
-- Discrepancy: +437,000 rows after September 14 refresh

-- Diagnosis Step 2: Check for duplicate InvoiceID in FACT_INVOICES
-- Power BI Desktop → DAX Query view
EVALUATE
ADDCOLUMNS(
    SUMMARIZE(FACT_INVOICES, FACT_INVOICES[InvoiceID], "RowCount", COUNTROWS(FACT_INVOICES)),
    "HasDuplicate", IF([RowCount] > 1, "YES", "NO")
)
-- Result: 437,000 InvoiceIDs appear more than once (2 or 3 rows each)
-- 410,293 InvoiceIDs appear exactly once

-- Diagnosis Step 3: Check source view on SQL Server
-- SQL Server Management Studio → BPSG-SQL-01 → BPSG_Finance → Views
-- → dbo.vw_InvoiceDetails → Script View as → CREATE TO

-- Current view definition (post-September 14):
-- SELECT (no DISTINCT) FROM INVOICES JOIN INVOICE_LINE_ITEMS LEFT JOIN PAYMENT_SPLITS
-- Row count: 1,284,293

-- Step 4: Check SQL Server DDL change date via default trace
SELECT EventClass, TextData, StartTime, LoginName
FROM sys.traces t
CROSS APPLY sys.fn_trace_gettable(t.path, DEFAULT)
WHERE EventClass = 164  -- Object:Altered
  AND ObjectName = 'vw_InvoiceDetails'
ORDER BY StartTime DESC;
-- Result: ALTER VIEW dbo.vw_InvoiceDetails, September 14 2026 09:23:41, DBA login

-- Step 5: Confirm PAYMENT_SPLITS table presence (confirms DISTINCT was needed)
SELECT COUNT(DISTINCT InvoiceID) AS InvoicesWithSplits, COUNT(*) AS TotalSplitRows
FROM dbo.PAYMENT_SPLITS;
-- InvoicesWithSplits: 437,000
-- TotalSplitRows:     921,154  (avg 2.108 splits per invoice)

Diagnosis was complete in 1.5 hours. The source of the inflation was unambiguous: SELECT DISTINCT removal on September 14 had allowed PAYMENT_SPLITS join rows to appear as duplicate invoice rows in the view, and the Power BI Import dataset had loaded all 1,284,293 rows every day since without detecting the change.

The fix: restoring DISTINCT to the source view and refreshing the Power BI dataset

Part 1: Restore DISTINCT to the source SQL Server view. The DBA restored the SELECT DISTINCT clause to dbo.vw_InvoiceDetails in a coordinated change with the Power BI retainer developer. After the view was updated, a spot-check query confirmed the row count returned to 847,293. The DBA also added a view-level comment documenting that SELECT DISTINCT was required because the PAYMENT_SPLITS join produces multiple rows per invoice when payment plans are used, and the view is designed to return one row per invoice by projecting only invoice-level (not split-level) columns.

-- Fix: Restore SELECT DISTINCT to dbo.vw_InvoiceDetails
ALTER VIEW dbo.vw_InvoiceDetails AS
SELECT DISTINCT
    i.InvoiceID,
    i.ClientID,
    i.InvoiceDate,
    i.InvoiceAmount,
    i.ServiceLine,
    i.SalesRepID,
    i.InvoiceStatus,
    i.DueDate,
    i.PaidDate
    -- NOTE: SELECT DISTINCT required — PAYMENT_SPLITS join creates 1 row
    -- per split per invoice; this view projects invoice-level columns only.
    -- Removing DISTINCT inflates row count by avg 2.108× for split invoices.
FROM dbo.INVOICES i
JOIN dbo.INVOICE_LINE_ITEMS li  ON li.InvoiceID = i.InvoiceID
LEFT JOIN dbo.PAYMENT_SPLITS ps ON ps.InvoiceID = i.InvoiceID;

-- Verify row count after fix
SELECT COUNT(*) FROM dbo.vw_InvoiceDetails;
-- 847,293  ✓ (matches pre-September 14 row count)

SELECT COUNT(DISTINCT InvoiceID) FROM dbo.vw_InvoiceDetails;
-- 847,293  ✓ (confirms no duplicates: row count = distinct invoice count)

Part 2: Manual Power BI dataset refresh and revenue validation. The Power BI dataset was refreshed manually via the Power BI Service “Refresh now” button. After the refresh completed in 4 minutes 17 seconds, the dataset row count returned to 847,293. The September 2026 Revenue KPI updated to $6,384,293 — matching the AR ledger. All revenue measures across all reports were validated against the AR ledger for the September and October months; all values confirmed correct.

-- Post-fix validation in Power BI Desktop (DAX Query view)
EVALUATE
ROW(
    "TotalInvoiceRows",   COUNTROWS(FACT_INVOICES),
    "DistinctInvoiceIDs", DISTINCTCOUNT(FACT_INVOICES[InvoiceID]),
    "SepRevenue",         CALCULATE([Total Revenue], FACT_INVOICES[InvoiceDate] >= DATE(2026,9,1),
                                                     FACT_INVOICES[InvoiceDate] < DATE(2026,10,1))
)
-- Before fix: TotalInvoiceRows=1,284,293, DistinctInvoiceIDs=847,293, SepRevenue=$9,695,273
-- After fix:  TotalInvoiceRows=847,293,   DistinctInvoiceIDs=847,293, SepRevenue=$6,384,293  ✓

-- Cross-check against AR ledger (provided by finance team):
-- September 2026 AR Ledger total invoiced:       $6,384,293
-- Power BI September 2026 Revenue (post-fix):    $6,384,293  ✓  delta = $0

Part 3: Post-incident monitoring setup. A Power BI row-count monitoring measure was added to the dataset: Invoice Row Count = COUNTROWS(FACT_INVOICES), displayed on a hidden monitoring page. A Power BI alert was configured on a single-number card using this measure: if the value exceeded 900,000 (6% above the expected ~847,293 maximum under normal business growth), a notification email would be sent to the Power BI workspace admin. Additionally, the SQL Server DBA team agreed to include Power BI dependency impact in their view modification checklist: any ALTER VIEW or DROP VIEW on objects in the BPSG_Finance database would require notification to the Power BI retainer developer before the change was applied to production.

Total engagement time: 3.5 hours (1.5h diagnosis: Power BI dataset row count check in Data view, DAX duplicate InvoiceID query, source view definition inspection in SQL Server, DDL change date retrieval via default trace; 1.0h fix: DBA restores DISTINCT, view row count spot-check, manual Power BI dataset refresh, revenue KPI validation against AR ledger for September and October; 0.5h monitoring: row-count alert configuration in Power BI Service, Power BI dependency notification procedure added to DBA change management checklist; 0.5h documentation and retainer SOP update).

Why source view schema drift is a recurring Power BI retainer problem

The Bridgewater Professional Services incident demonstrates the structural dependency that every Power BI Import dataset has on its SQL Server source objects: the dataset is only as clean as the views and tables it reads from. Power BI Import mode performs no row-count validation, no deduplication check, and no data distribution comparison between refreshes. From Power BI’s perspective, receiving 437,000 additional rows is indistinguishable from 437,000 new invoices being issued. The scheduled refresh engine reports success, the dataset appears current, and every downstream measure returns mathematically correct results — relative to the data it was given.

The SELECT DISTINCT clause in a SQL Server view is a semantic signal: it communicates that the view’s join structure intentionally produces duplicate rows and that the view is designed to collapse them. When a DBA removes that clause without understanding its purpose, the view’s row-count behavior changes silently. The fact that the DBA checked PAYMENT_SPLITS for rows and found records (correctly identifying the duplication source) but still removed DISTINCT — because the payment split data was real and the DBA considered DISTINCT redundant for “real” data — illustrates how the semantic intent of view design can be lost without explicit documentation. The comment added during the fix (“SELECT DISTINCT required — PAYMENT_SPLITS join creates 1 row per split per invoice”) is the documentation that should have been there from day one.

A Power BI developer retainer who runs a quarterly source object audit — comparing the DDL of all SQL Server views in the Power BI data source against the view definitions as of the last audit, and checking for row-count changes greater than 10% versus the rolling 7-day average — catches this class of issue within days of the source change rather than weeks.

How HourTab helps Power BI developer retainers

The Bridgewater Professional Services engagement totaled 3.5 hours: diagnosis (1.5h — 0.25h checking the FACT_INVOICES row count in Power BI Desktop Data view and noting the discrepancy from the prior week’s known count of 847,293; 0.25h running a DAX duplicate InvoiceID query confirming 437,000 InvoiceIDs appear more than once; 0.25h opening Power BI Desktop Power Query Editor and inspecting the M query source reference to identify dbo.vw_InvoiceDetails; 0.25h connecting to SQL Server Management Studio and reviewing the current view definition, confirming SELECT DISTINCT had been removed; 0.25h running a SQL Server default trace query to confirm the ALTER VIEW event on September 14 2026 and the DBA login responsible; 0.25h checking PAYMENT_SPLITS row count and distribution to confirm the duplicate mechanics), fix (1.0h — 0.25h coordinating with DBA to restore SELECT DISTINCT and verify view row count returns to 847,293; 0.25h triggering manual Power BI dataset refresh via Power BI Service and monitoring completion; 0.25h running post-fix DAX validation: COUNTROWS confirms 847,293 rows, DISTINCTCOUNT([InvoiceID]) confirms 847,293 distinct invoices, September revenue KPI confirms $6,384,293; 0.25h cross-checking September and October revenue against AR ledger values provided by finance team — all values confirmed), monitoring setup (0.5h — 0.25h adding Invoice Row Count measure to a monitoring report page and configuring Power BI Service data alert on row count card for threshold > 900,000; 0.25h adding Power BI view dependency review step to DBA change management checklist, distributing to DBA team), and documentation (0.5h — incident write-up for retainer file; retainer SOP updated to add quarterly source view DDL comparison audit for all M query source references in the BPSG_Finance database; AR cross-check procedure added as monthly pre-board-report validation step).

In a HourTab retainer log, the incident session reads: “Power BI FACT_INVOICES row count: 1,284,293 (expected ~847,293); DAX duplicate check: 437,000 InvoiceIDs appear 2–3 times; M query source: dbo.vw_InvoiceDetails; SQL Server: SELECT DISTINCT removed from view September 14 2026 by DBA (performance optimization — DBA unaware of PAYMENT_SPLITS join behavior); PAYMENT_SPLITS has 921,154 rows for 437,000 invoices; duplicate rows inflate all SUM([InvoiceAmount]) measures by 1.515×; September 2026 revenue shows $9,695,273 instead of actual $6,384,293 ($3.3M inflated for 37 days); discovery: October 21 CFO AR cross-check; fix: DBA restores SELECT DISTINCT; Power BI manual refresh; row count 1,284,293 → 847,293; September revenue $6,384,293 confirmed vs AR ledger; monitoring: row count alert >900,000; DBA change checklist updated; 3.5h.” The CFO sees the exact source of the inflation (a SQL Server view change that Power BI had no visibility into), the scope of impact (37 days, 51.8% revenue inflation, every SUM measure in the workspace), and the fix applied at both layers (SQL Server view and Power BI dataset refresh) — with no ambiguity about how the retainer time was spent.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Power BI developer retainers

See also: dbt developer retainer · Snowflake developer retainer · Azure Data Factory developer retainer