PowerPivot - making connection between tables

[Marusya_lam]

Hi all, good day!

Thre are 2 tables exported in PowerPivot. For convenience it's SAP delivery with Names of products (from different Distributors) - "table 1" - and master-sdata table with matching Russian names of products/English names of prosducts - "table 2". So I want to make connection in PP between Names (table 1) and Russian names of Products (table 2). But I see error - [FONT=open_sans]"The relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values". I checked 10 times in Excel (and also in MS Access making data model. The list of Russian mnames of products form tabble 2 is unique (I also TRIMed it in Excel JIC. Maybe somebody faced with the same problem?

Thanks in advance.

Maria[/FONT]

 

[scottsen]

Well, if nothing else, you certainly cemented that my next blog post will be about relationships

So, in a perfect world, you have a "data table" (where you have numbers, likes Sales$) and you have some lookup tables, where you have say, Product info or Geography info.

And these relationships are 1:many. A given Product... { Id=123, Name=Slippers } will only show up in your Products table one time, but you will have many sales for that product.

What you are trying to do is a many:many and that generally... no worky.

You really want to get your two tables.. into 1 table... before you get to power pivot. Power Query could likely help there if you aren't in SQL land.

 

[Marusya_lam]

Hi all,

There were 9 non-printing characters which prevented to remove duplicates. Early I tried to remove it in usual Excel by 5 different methods. Only Pivot table through PowerPivot and DistictCount function helped me to find it.