Forum Discussion

Mohamadmonem's avatar
Mohamadmonem
Icon for Advocate I rankAdvocate 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...
Need Help
  • Zanqueta's avatar
    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

    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 🌀.

Ray_Minds's avatar
Ray_Minds
Icon for Solution Supplier rankSolution 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.