Columns contains duplicate values RRS feed

  • Question

  • Hi,

    I am connecting a Fact table to a lookup table. The lookup table is coming from database with a SELECT DISTINCT so that making sure all records are unique. When I create the connection from the fact to the lookup table PowerPivot says the relationship cannot be created because each column contains duplicate values. How is this possible considering that I have trimmed down the lookup by using a SELECT DISTINCT? 

    Thursday, July 4, 2019 6:25 PM

All replies

  • It is likely to be either... There are blanks, There are nulls, There are lower case and upper case representations of the key.

    Self Service BI Expert using Power Pivot

    Thursday, July 4, 2019 9:42 PM
  • Thank you Matt. So far I have not been able to verify that is the case, but you are right it is very likely. What would you recommend? should I take it to Power Query? I tend to do these stuff in SQL while import if possible. 
    Thursday, July 4, 2019 10:56 PM
  • Well I normally drop the list of keys into Excel and run a pivot table over the top.  But yes, Power Query could help too. Once you know the issue, you can change the query to manage it.

    Self Service BI Expert using Power Pivot

    Friday, July 5, 2019 2:05 AM