Can't create relationship due to duplicate values (even after removing duplicates in Power Query)

[Mohamadmonem]

Hi everyone,
I'm trying to create a relationship between two tables using a CostKey column in Power BI, but I keep getting this error:

"Column 'CostKey' in table contains a duplicate value and this is not allowed for columns on the one side of a many-to-one relationship..."

Here's what I’ve done:

1) I created the CostKey field by combining two columns:

CostKey = Text.Trim([COMP_CODE]) & "-" & Text.Trim([ITEM_CODE])

2)I then used:  Table.Distinct(..., {"CostKey"})

to remove duplicates. In Power Query preview, I see only unique values in the CostKey_Lookup table.

However, when I load the data model and try to create a relationship, Power BI still shows a duplicate key error.

Additional info:

• I confirmed that there are no blank/null CostKey values.

• I sorted the table in data view and couldn't find any duplicate CostKey.

• Tried adding Text.Upper() and Text.Trim() to ensure formatting consistency.

• Even tried publishing the semantic model to the Power BI service and connecting via a composite model, but got other limitations (DirectQuery not supported).

• How can I ensure a clean one-to-many relationship if Power Query shows all distinct keys?

  Thanks in advance!

[lbendlin]

Nulls are not allowed on the "one"  side of the relationship.  Check again.

Also note that Power Query is case sensitive while Power BI is not.

[cengizhanarslan]

Make Table.Distinct the very last step:
Duplicates often come back after a later merge/expand step. Put this last:

= Table.Distinct(#"Previous Step", {"CostKey"})

Prove duplicates with Group + Count:

let
  g = Table.Group(#"Previous Step", {"CostKey"}, {{"Cnt", each Table.RowCount(_), Int64.Type}}),
  d = Table.SelectRows(g, each [Cnt] > 1)
in
  d

If this returns rows, you still have duplicates (even if you can’t spot them in Data view).

If it returns no rows, then it’s usually hidden characters. Build the key with Text.Clean + remove non-breaking space:

CostKey =
let
  c = Text.Upper(Text.Clean(Text.Trim(Text.From([COMP_CODE])))),
  i = Text.Upper(Text.Clean(Text.Trim(Text.From([ITEM_CODE])))),
  c2 = Text.Replace(c, Character.FromNumber(160), ""),
  i2 = Text.Replace(i, Character.FromNumber(160), "")
in
  c2 & "-" & i2

Once the Group+Count shows no duplicates and Distinct is last, the relationship will create.

[AnkitKukreja]

Hi! @Mohamadmonem 

When you load a distinct table, check in the table view how many rows are there and how many distinct rows are shown at the bottom left. This will give you an idea of whether there are any duplicates. Also, if you are already doing distinct and it still cause duplicate issue, try removing duplicates step as well in PQ and then try to create a relationship.

[Zanqueta · Accepted solution]

Hi @Mohamadmonem,

Try start filtering by "keep duplicates" in CostKey column to see where this are happening.

Power Query shows unique values, but the data model still detects duplicates. The reason is usually one of these:

---

✅ Why this happens

1. Table.Distinct only applies to the query preview
   If you applied Table.Distinct in Power Query but did not disable “Enable load” for other queries or did not reference the correct query in the model, duplicates may still exist in the table that is loaded.
2. Hidden duplicates after load
   • Power BI relationships require the “one” side to be truly unique after all transformations and load.
   • If you have multiple queries referencing the same source or if the lookup table is not the one you applied Distinct to, duplicates can persist.
3. Data type mismatch or trailing spaces
   Even if you trimmed and uppercased, check that both tables use the same data type (Text vs. Text) and that there are no invisible characters (non-breaking spaces).

---

✅ How to fix it

Enforce uniqueness in Power Query

• Ensure the query you load to the model is the one with Table.Distinct.
• Example:

let
    Source = ...,
    AddedKey = Table.AddColumn(Source, "CostKey", each Text.Trim([COMP_CODE]) & "-" & Text.Trim([ITEM_CODE]), type text),
    DistinctRows = Table.Distinct(AddedKey, {"CostKey"})
in
    DistinctRows

Confirm that only this query is loaded (disable load for intermediate queries).

Use a surrogate key

If duplicates are unavoidable (e.g., same CostKey for different contexts), create a surrogate key in the lookup table and use that for the relationship.

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.