Powerpivot cannot detect relationships

[Powerpivoteer]

Dear users, I have 2 sets of data.
Dataset01 = sales data from company
Dataset02 = 2 column set (customer = text & channel = text)
Dataset01 contains column = Customer (text) but with repeated values (since it is sales data)
I want to bring the Channel information from dataset02 to dataset01 beside every value of customer appearing.
BUT the powerpivot keeps saying "the columns you have chosen for the relationship contain duplicate values...... choose at least 1 column that does not contain duplicate values!".

I can' understand why.
Thank you all.
Kind Regards

 

[pete234]

If you have 2 tables one of which has unique customer id or text value (DataSet02), this shouldn't happen as long as you choose the customer field from your unique table and not the one with dupes and join on the Customer field. If you are getting the error when making the relationship, there is something wrong with the data. Make a pivot of just Set 2 to see if you can spot it or try to remove dupes in regular worksheet.
Then if it looks like that won't supply unique data, make your own unique customer table from another data source from the company and join this other sets to that set and use it's field in the pivot.

 

[Powerpivoteer]

Dear pete234 thank you for the answer - I will check the set02, but for making the relationship > Do I need a 3rd table (join) or do I do it directly with those 2 sets? I have forgotten to ask that I apologize. Thank you.

 

[pete234]

Yes if you can't get set02 to be unique, make a 3rd table between these 2 and make a relationship on the unique value in this new table. Use the middle table for that value, don't pull it from the other tables. For instance, I think you want customer name to be unique. So when you make the pivot, use the Customer Name from the middle-table after you join them. Then drop your channel field in.