Forum Discussion

S_M's avatar
S_M
Helper III
9 years ago

Unable to create relationships between tables

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 ...
Need Help
kaushikd's avatar
kaushikd
Resolver II
9 years ago

Hi S_M

Could you please explain the scenario with example :-

Like 

Dataset 1

id   xyz   abc

1    a      b

 

Dataset 2

id   xyz   abc

1    a      b

 

Relationship between Dataset 1 and Dataset 2 

and in which step you are facing challenge.

 

S_M's avatar
S_M
Helper III
9 years ago

Hi kaushikd,

To break it down : I have a sales table (Table1):

order_number  customer_email

1                       [email protected]  

2                       [email protected]

3                       [email protected]

4                       [email protected]

 

 

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 : 

 

Table2:

 

customer_email

[email protected]

[email protected]

[email protected]

 

 

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 

 

  • kaushikd's avatar
    kaushikd
    Resolver II
    9 years ago

    S_M

     

    Hi I have taken the same data as you have provided:-

    and tried the following followed the steps:-

     

    1. Imported the Table1

    order_number  customer_email

    1                       [email protected]  

    2                       [email protected]

    3                       [email protected]

    4                       [email protected]

    from excel file

     

    2. Go to Edit Query--> Right Click on Table1-->Select Duplicate-->Renamed the new Table as Table2

    3. For Table2-->Right Click on order_number Column and Remove.-->Right Click on customer_email Column and Remove Duplicates.

    4. Close and Apply.

    5.Go to Relationship 

    you will find power bi will automatically do a join for both the table or else please do it by your own.

     

     

    If this help you out please accept this as a solution thanks

     

    • S_M's avatar
      S_M
      Helper III
      9 years ago

      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.

       

      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 ?