A “Primary Key” violation occurs in Excel when you load data into the Power Pivot Data Model and create a one-to-many relationship, but the table on the “one” side of the relationship contains duplicate or null values.
The Power Pivot Data Model requires that the primary key column in a lookup table (such as DimCustomer[CustomerID] or DimProduct[ProductSKU]) contain strictly unique entries. If even a single duplicate ID, blank cell, or trailing-space variant exists in that key column, the Data Model rejects the relationship and halts during the load phase with an error stating that the column contains duplicate values.
How to Fix Primary Key Violations in the Excel Data Model
To fix a primary key violation, open the lookup query in Power Query, select the primary key column, and choose Remove Rows > Remove Duplicates. If duplicate values were caused by leading or trailing whitespace, apply Transform > Format > Trim and Clean before removing duplicates to ensure unique text keys are recognized correctly.
Why the Power Pivot Engine Rejects Duplicates
In relational database modeling, a One-to-Many relationship requires that every row in the dimension table have an unambiguous identity:
[DimCustomer] (One Side) [FactSales] (Many Side)
CustomerID (Must be Unique) CustomerID (Can Repeat)
─────────────────────────── ────────────────────────
101 ───────── James Smith <───┐ 101 (Jan 2 Sales)
102 ───────── Maria Garcia └── 101 (Feb 14 Sales)
103 ───────── Robert Chen 102 (Mar 10 Sales)
101 ───────── Duplicate! 103 (Apr 22 Sales)
▲
└── Power Pivot halts: Engine cannot determine which "101" to link
If Power Pivot permitted duplicate keys in DimCustomer, calculating DAX measures like RELATED(DimCustomer[CustomerName]) would become ambiguous. The engine cannot know which customer profile belongs to invoice 101. As a result, the load process fails to protect relationship integrity.
Step 1: Detect Duplicate Values in Power Query
Rather than hunting for duplicates inside the full Excel grid, use Power Query’s built-in column profiling tools to find the offending records.
- Go to Data > Queries & Connections and double-click the dimension/lookup table query.
- In the Power Query ribbon, go to the View tab.
- Check the Column distribution and Column profile boxes.
- Select your primary key column.
- In the distribution statistics pane below the column:
- Inspect the Distinct and Unique counts.
- If the total row count is
10,000, the Distinct count must be10,000. If Distinct is lower than the row count, duplicate values exist.
Column Profile Pane:
Count: 10,000
Distinct: 9,998 <─── Indicates 2 duplicate records
Unique: 9,996
Empty: 0
Step 2: Clean Whitespace and Case Variations
In many cases, database exports do not contain true duplicates; instead, formatting discrepancies make distinct entries look identical, or identical entries look distinct to the engine.
- Case Sensitivity: SQL Server databases often operate on case-insensitive collations (e.g.,
SQL_Latin1_General_CP1_CI_AS), treating"A100"and"a100"as the same record. Power Pivot, however, is case-insensitive in relationships, meaning that if SQL passes both"A100"and"a100"as separate records, Power Pivot flags them as duplicates! - Trailing Spaces:
"SKU-99 "and"SKU-99"appear distinct during extraction, but transformations may collapse them later, causing unexpected collision.
The Remediation Sequence in Power Query:
- Select the primary key column.
- Go to Transform > Format > Clean to strip non-printable characters.
- Go to Transform > Format > Trim to eliminate leading and trailing spaces.
- Go to Transform > Format > UPPERCASE to resolve mixed-case collisions.
- With the column still selected, click Home > Remove Rows > Remove Duplicates.
If working with pivot transformations that introduce duplicate row groupings, refer to Fixing errors when using Table.Pivot on non-unique values..
Step 3: Handle Nulls and Blanks in Dimension Keys
In relational modeling, a primary key cannot be NULL. If your SQL dimension table contains unassigned records, orphaned rows, or blank entries, Power Pivot treats multiple null cells as duplicate values.
CustomerID
──────────
101
102
null <── First Null (Allowed)
null <── Duplicate Null! (Triggers Primary Key Violation)
Option A: Filter Out Nulls
If records without an ID are junk data or inactive accounts:
- Click the filter dropdown arrow on the primary key header.
- Uncheck (null) or select Remove Empty.
Option B: Replace Nulls with a Default Surrogate Key
If business logic requires retaining rows with missing IDs:
- Right-click the column and select Replace Values.
- Set Value To Find to
null. - Set Replace With to
1(if numeric) or"UNKNOWN"(if text). - Ensure your Fact table maps missing foreign keys to that same replacement value (
1or"UNKNOWN").
For a complete breakdown of how missing values compromise relationship chains, see Handling “Null” values that break Power Pivot “Relationship” integrity..
Step 4: Manage High-Volume Memory Breaks During Deduplication
When working with large tables containing millions of rows, executing a Table.Distinct operation inside Power Query can overwhelm local system memory if the query does not fold back to the database.
If deduplication causes Excel to freeze during the refresh process:
- Check whether your source step supports query folding so the SQL server executes the
DISTINCTaggregation before sending data over the network. - If memory constraints halt the load, follow the diagnostic steps in Fixing “Table.Distinct” errors when memory limits are hit..
Once primary keys are unique, verify that the data types match across both tables before rebuilding the relationship; otherwise, you will encounter the secondary issue documented in Troubleshooting “Data Type Mismatch” between SQL Server and Power Pivot.. If your DAX measures still return missing-value errors after establishing relationships, see #N/A in Power Pivot Relationship Mismatches.