Fix "column contains a duplicate value" in Power BI refresh

Power BI refresh fails: a column contains a duplicate value on the one side of a relationship. Find the hidden duplicates (case, spaces, changed dates) and fix them.

Power BIFix a problem5 min read

Checked against Microsoft Learn 21 Oct 2026 · 5 sourcesPosted by the Maker DeskHow we write guides

The refresh worked yesterday, and today it stops with a long message about a duplicate value. The message is exact about what went wrong. The confusing part is that when you look for the duplicate in Power Query, it often isn't there.

Refresh fails with Column 'ProductCode' in Table 'Product' contains a duplicate value 'AB-100' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
It fails in Power BI Desktop and in the service: one bad value stops the whole refresh, not just one table.
Power Query's Remove duplicates step is already there, or Keep duplicates finds nothing.

What the message means

A one-to-many relationship needs every value in the "one" column (usually the key of a dimension table, such as Product[ProductCode]) to be unique. Microsoft's relationship documentation is blunt about what happens otherwise: if a refresh tries to load a duplicate value into a "one" side column, the entire data refresh fails. The message names the column, the table and the first duplicate it met. There may be more behind it.

Why Power Query says there are no duplicates

Power Query and the model that stores your data compare text differently:

	Power Query	The model (where the relationship lives)
Capital letters	Case-sensitive: "ab-100" and "AB-100" are different	Case-insensitive: they're the same value
Spaces at the end	Kept: "AB-100 " is different	Trimmed: "AB-100 " becomes "AB-100"
Spaces at the start	Kept	Kept: " AB-100" stays different

So a key column can pass Power Query's Remove duplicates (which is case-sensitive, as Microsoft's Power Query documentation warns) and still collide the moment it loads into the model. Hand-typed codes, SharePoint lists and exports from systems that don't care about case are the usual source.

How a "unique" key becomes a duplicate

Pause animation

Power Query sees "AB-100" and "ab-100 " as two values
Remove duplicates keeps both
The model ignores case and trailing spaces
Both become "AB-100" on the one side
The refresh fails

Find the duplicates

Compare the keys the way the model does: ignore case and trailing spaces. In Power Query, create a blank query (Home > New source > Blank query), open the Advanced editor, and paste this, changing Product and ProductCode to your table and column:

powerquery

let
    Source = Product,
    AsTheModelSeesIt = Table.AddColumn(Source, "KeyCheck", each Text.Upper(Text.TrimEnd([ProductCode])), type text),
    Grouped = Table.Group(AsTheModelSeesIt, {"KeyCheck"}, {{"Rows", each Table.RowCount(_), Int64.Type}}),
    Clashes = Table.SelectRows(Grouped, each [Rows] > 1)
in
    Clashes

Every row it returns is a key that the model will treat as a duplicate. Right-click the query and turn off Enable load, so it stays a check and doesn't become a table in your model.

Fix it in Power Query

In the dimension query (the "one" side), clean the key: select the column, then Transform > Format > Trim and Transform > Format > UPPERCASE. In M, that's Table.TransformColumns(Source, {{"ProductCode", each Text.Upper(Text.Trim(_)), type text}}).
Then add Remove duplicates on the key column. The order matters: removing duplicates before cleaning leaves the near-duplicates in.
Apply the same cleaning to the key in the fact query (the "many" side). If you only clean one side, "ab-100" sales rows no longer match "AB-100", and they turn up under a blank row in your visuals.
Refresh in Desktop, then publish.

If the clashing rows are genuinely different things that share a code, removing duplicates hides a data problem. Fix the codes at the source, or pick a key that really is unique.

Clean keys with Trim and a single case on both sides of every relationship built on text.
Keep the "find the duplicates" query, with load turned off, for the next time.
Fix it in the source system when people type codes by hand.

Don't

Don't switch the relationship to many-to-many to make the error go away: each sale then matches every product row that shares its code, so it can show under both products, and the bad data stays.
Don't rely on Remove duplicates alone for text keys: it's case-sensitive, the model isn't.

With incremental refresh

On a table with incremental refresh, the data is split into date partitions, and only recent partitions refresh. Microsoft's troubleshooting guide describes the trap: if a row's transaction date is changed at the source, the same ID can end up in an old partition and a new one, and a table used as the "one" side then fails with a duplicate value. Keep transaction dates fixed once written; if old dates must change, refresh every partition from the changed one onwards (for example with SQL Server Management Studio over the XMLA endpoint) so the ID exists only once.

DirectQuery behaves differently

With DirectQuery there's no load step, so there's no error message either. Microsoft notes that Power BI assumes the source has no duplicates on the "one" side, and with a case-sensitive source the results are undefined: numbers can be quietly wrong. Normalise the case in the source query or in Power Query there too.

Still stuck after the keys are clean? Our refresh failures checklist covers the other common refresh errors, and the key didn't match any rows explains a different message that looks similar.

Sources

Did this fix it?

Comments

No comments yet. Share what worked for you.