Unable to create relationships between tables

Question

I am pulling a customer table which is basically a sales table with all columns, except customer email, removed. I then 'remove duplicates' on the customer email column in Editor, essentially giving me a distinct list of all customer emails that have made a purchase with us. However, when i try to create a relationship between this column and 'customer email' in the sales table, i get an error stating that one of the 2 columns being related must be without duplicates - as i already do a distinct on the customer table, this should not happen.

Does anyone know if using a 'remove duplicates' is the reason this issue occurs and if there is a work around ?

Thanks!

s_m · Answer

Hi&nbsp;kaushikd,To break it down : I have a sales table (Table1):order_number &nbsp;customer_email1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; abc@xyz.com &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; def@xyz.com3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ghi@xyz.com4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; def@xyz.com&nbsp;&nbsp;I use Table1 to create a new customers table (Table2) which only has the row 'customer_email', removing duplicates, so essentially it looks like this :&nbsp;&nbsp;Table2:&nbsp;customer_emailabc@xyz.comdef@xyz.comghi@xyz.com&nbsp;&nbsp;I am now trying to relate Table2 to Table1 using 'customer_email' as primary key, however it doesnt allow me to create a relationship by saying that both tables do not contain distinct values. So i am stumped at why this would happen and how to go about it&nbsp;&nbsp;

kaushikd · Answer

S_M&nbsp;Hi I have taken the same data as you have provided:-and tried the following followed the steps:-&nbsp;1. Imported the Table1order_number &nbsp;customer_email1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; abc@xyz.com &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; def@xyz.com3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ghi@xyz.com4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; def@xyz.comfrom excel file&nbsp;2. Go to Edit Query--&gt; Right Click on Table1--&gt;Select Duplicate--&gt;Renamed the new Table as Table23. For Table2--&gt;Right Click on&nbsp;order_number Column and Remove.--&gt;Right Click on&nbsp;customer_email Column and Remove Duplicates.4. Close and Apply.5.Go to Relationship&nbsp;you will find power bi will automatically do a join for both the table or else please do it by your own.&nbsp;&nbsp;If this help you out please accept this as a solution thanks&nbsp;

kaushikd · Answer

Hi&nbsp;S_MCould you please explain the scenario with example :-Like&nbsp;Dataset 1id &nbsp; xyz &nbsp; abc1 &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp;b&nbsp;Dataset 2id &nbsp; xyz &nbsp; abc1 &nbsp; &nbsp;a &nbsp; &nbsp; &nbsp;b&nbsp;Relationship between Dataset 1 and Dataset 2&nbsp;and in which step you are facing challenge.&nbsp;

ovetteabejuela · Answer

I would also try to do a little validation.&nbsp;You can export the table to excel and do a Remove Duplicates there and see if it did find duplicates.

s_m · Answer

kaushikd, unfortunately this is similar to what ive done and i face the problem above, which is that i am unable to entirely remove duplicates.&nbsp;ovetteabejuela, good idea! I actually did that and found one sneaky duplicate that is somehow not being detected by PowerQuery when removing duplicates. On excel, these duplicates get highlighted, however i ran some trim / length functions to understand why it is not being detected as a duplicate, but they are exactly alike. Any idea how i can remove these on editor ?&nbsp;

Forum Discussion

Unable to create relationships between tables

6 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships