Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

You can't create a relationship between these two columns...

I need some help figuring out why I continue to recieve the "Create Relationship" error "You can't create a relationship between these two columns because one the columns must have unique values."

The 2 columns are Serial Numbers one is the data table the other is a LookUp table I built by taking the column form the main data table and removing duplicates and Blank/empty rows.  I need to match up the main data table and secondary data table that has these serial numbers, but can't get the Lookup data to match up with data tables.  

The main data table has ~450k rows

The Lookup table is around ~175K rows

 

Am I missing something?

Need Help
  • swise001's avatar
    swise001
    6 years ago

    Anonymous 

     

    Check that both columns you are using as part of the relationship are of the same data type (Text, Whole Number, etc).  Sometimes if one column is still labeled as 'any'  it can cause issues.  Also - you can try to trim/clean the columns prior to removing duplicates.  

9 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous are you sure it is distinct and there is no blank/null value or empty value?

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      Anonymous drop the serial number from a lookup table in a table visual and also add countrows on serial number and sort if by count, and see if there are no duplicates with value count more than 1, and also check no blank serial number by sorting by serial number.

       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

      ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi perry2k
      I can confirm that no Nulls or Empty rows.  I pulled the data in excel, removed the duplicates and blank rows.  Then moved it into a new sheet with only non-blank values and pulled that data into Power BI and recieved the same error.

       
      • Watsky's avatar
        Watsky
        Icon for Solution Sage rankSolution Sage
        6 years ago

        Anonymous What does your Column statistics look like if you set your column profiling based on the entire data set?  If you don't have this turned on you can turn it on in Power Query then click view and check the box for Column Profile.

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous.

    Perhaps you can try to extract two table serial number value and create a bridge table with unique value to link these two tables, it should prevent the relationship analysis issues.
    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I have extracted the data, but no change.  It's like Power BI has determined that there are too many values in the table for there to be unique values in it.  The table has 173K items, all unique.

  • swise001's avatar
    swise001
    Icon for Continued Contributor rankContinued Contributor
    6 years ago

    Anonymous 

     

    Check that both columns you are using as part of the relationship are of the same data type (Text, Whole Number, etc).  Sometimes if one column is still labeled as 'any'  it can cause issues.  Also - you can try to trim/clean the columns prior to removing duplicates.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Changed to Text, that resolved the issue.