none
ERROR: The relationship cannot be created because both columns selected contain duplicate values. RRS feed

  • Question

  • Hi. I faced a big problem (for me) right now; I can't create any relationship in powerpivot.

    The error says "The relationship cannot be created because both columns selected contain duplicate values."

    But I am now working with big data(22000lines×3sheets) so I cannot really change my data.

    And even I do not know how to change my data to be able to create the relationship and make a good analytical table.

    Could anyone give me suggestion to solve this problem?

    I just need to create relationship :/

    Friday, December 2, 2016 3:25 AM

Answers

  • Hi Ritax,

    According to the error message, both columns contains duplicate values. For creating relationship between tables, you need have at least one column that contains only unique values. You will need to eliminate the duplicate, otherwise the Power pivot will not work. You can delete the duplicate values by Power Query.

    In the Edit Queries Edit window. Select your primary key column and from the Home ribbon, choose Remove Rows / Remove Duplicates. The duplicate values will be deleted. Please see the following screenshot.



    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, December 5, 2016 5:27 AM
    Moderator
  • Hello RitaX, 

    I think it is a great opportunity for you to start familiarizing yourself with bridge tables. Since tabular semantic models do not support directly  many to many relationships  we are forced to get a little creative and introduce bridge tables into our models. A bridge table is nothing but a unique dataset from both columns (keys) where you have a many to many relationship. 

    You need to create a separate table with unique values from your key columns (probably by appending key columns and then using excel Remove Duplicates under DATA to get rid of duplicates or another option is removing duplicates through Power Query. (It's just one of the options you are welcome to get creative here. The point is to end up with a column containing only unique values. Bring your  newly created bridge table into powerpivot  and establish connections to each table and that's it you can use the bridge table for your filtering. (Unfortunately, cross-filtering will not work in with this method)

    You can see below some of the screenshots how it is done. Good luck !

    At the end, I must say that there exists a much cleaner and simpler way to create bridge tables  but it evolves using Power BI Desktop which gives you a great power of cross-filtering.  If you are interested to see how it is done in Power BI Desktop please let us know.  




    Monday, December 5, 2016 6:29 AM

All replies

  • Hi Ritax,

    According to the error message, both columns contains duplicate values. For creating relationship between tables, you need have at least one column that contains only unique values. You will need to eliminate the duplicate, otherwise the Power pivot will not work. You can delete the duplicate values by Power Query.

    In the Edit Queries Edit window. Select your primary key column and from the Home ribbon, choose Remove Rows / Remove Duplicates. The duplicate values will be deleted. Please see the following screenshot.



    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, December 5, 2016 5:27 AM
    Moderator
  • Hello RitaX, 

    I think it is a great opportunity for you to start familiarizing yourself with bridge tables. Since tabular semantic models do not support directly  many to many relationships  we are forced to get a little creative and introduce bridge tables into our models. A bridge table is nothing but a unique dataset from both columns (keys) where you have a many to many relationship. 

    You need to create a separate table with unique values from your key columns (probably by appending key columns and then using excel Remove Duplicates under DATA to get rid of duplicates or another option is removing duplicates through Power Query. (It's just one of the options you are welcome to get creative here. The point is to end up with a column containing only unique values. Bring your  newly created bridge table into powerpivot  and establish connections to each table and that's it you can use the bridge table for your filtering. (Unfortunately, cross-filtering will not work in with this method)

    You can see below some of the screenshots how it is done. Good luck !

    At the end, I must say that there exists a much cleaner and simpler way to create bridge tables  but it evolves using Power BI Desktop which gives you a great power of cross-filtering.  If you are interested to see how it is done in Power BI Desktop please let us know.  




    Monday, December 5, 2016 6:29 AM
  • Hi,

    I am having this same problem - but I have no duplicates in one of my tables.  I have triple checked and "Removed Duplicates" several times.  I am still getting that message.  Any suggestions?

    Thank you!

    Angie

    Friday, June 15, 2018 4:25 PM
  • I wanted to post this for reference in case anyone else runs across this issue--I was having this same problem and it persisted after running through the steps to Remove Duplicates via Power Query. I solved it by reprocessing the table after removing duplicates, then I was able to create the relationship as expected.
    Monday, January 7, 2019 8:44 PM
  • I have the same problem, but I don't seem to understand what you mean by reprocessing. I refreshed it and deleted it from querries and then added it again, but I seem run into the same error.
    Sunday, July 12, 2020 4:31 PM