Forum Discussion

Advocate I

8 months ago

Solved

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

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!

Need Help

Zanqueta
8 months ago
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
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.
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.
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 🌀.

5 Replies

AnkitKukreja
Super User
8 months ago
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
  Super User
  8 months ago
  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
  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.
  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.
  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 🌀.
cengizhanarslan
Super User
8 months ago
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.
lbendlin
Super User
8 months ago
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.
Ray_Minds
Solution Supplier
8 months ago
Ans:

Tried to replicate your scenario:

Pull the CostKey column twice and select on count and then apply a filter for count > 1 – This will show each duplicate values

I have two duplicate values as I do not have applied distinct

To debug the issue I have deleted the relationship for fact and this dimension – In my case the issue solved with text.upper. As you mentioned, you have already tested it so would recommend you to identify the duplicate values as shown above.

If could also share a sample pbix that would be very helpful for us to fix the issue at the earliest !

If the provided solution has addressed your concern, please mark this post as the solution to assist other members who may encounter a similar issue.