Power Pivot doesn't connect all tables

Question

Hi,

I cant get my tables to connect in power pivot. I have SQL and ODBC databases loaded in pv and connected them all to make some pivot tables. Everything works accept adding a relation with the ODBC table. I want this to connect to a column but it says:

'the relationship cannot be created because each column contains duplicate values. Select at least one column containing only unique values.'

In the image you see the following connections:

(Artikel) art_nr -> art_nr (Archiefdervingjaar)

(Filiaal) fl_nr -> vestiging (Archiefdervingjaar)

(Periode) week -> week (kalender)

(Kalender) datum -> datum_nieuw (Archiefdervingjaar)

I want to connect (BVRD08) artikelnummer to (->) artikelnummer (Archiefdervingjaar) so i can show the comm min vrrd (BVRD08) in a pivottable but the powerpivot don't accept this relation.

The BVRD08 registers the set safety stock per item and per location. So there are several safety stock numbers per item, because we have 51 locations where we sell this product. So 51 different locations and 51 different set safety stocks on the same product number. we sell over 8000 items so its a lot of duplicates. How can i connect a relationship between location, product number AND safety stock number?

Answer 1

I hope someone can guide me the right way..

Answer 2

Hi @patrick kroezen

In my opinion, you need to create separate tables with unique values for location, product number and safety stock number. Then establish connections to each of these tables for your filtering.

As tag “Excel Mangement" focuses more on general issues of Excel client, if you have any issues of Power Query andd Power Pivot, please post a new thread on "Microsoft Power BI Community".

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---