Relationships Issue

[emma313823]

Hi All

I have an issue which I can't seem to figure out what is causing it.

I have pulled in two tables of customer related data of which a common relationship is the part number.  I pulled out a list of part numbers and created a unique table (all duplicates removed), so I can then create a one to many relationships from the unique part number list to the customer list. 

When I try to create a relationship from the unique part number talbe to the customer detail table, it tells me I can't create a relationship because I must have unique values and I'm 100% positive my part number table is unique values.  Both tables show the part numbers are set to text also.  Any idea how to fix this?  

[vanessafvg]

@emma313823 if you removed the duplicates in power query, it sees different cases of  the same value as different values

best thing to do is to standardize everything into one case, then remove duplicates

however i think null or blank values can also create issues so pad them with a default value like na or unknown

[emma313823]

The list of unique part numbers is in excel and I used 'get data' for this.  This spreadsheet is updated regularly, so it is easier to have the spreadsheet get updated then refresh the dashboard.

[KHorseman]

When you use Get Data to connect to an Excel sheet you can add a step that removes duplicates. Have you not done this?

Seriously try my check. You will see that there are duplicates.

[emma313823]

No didn't know I could do that...I removed duplicates using excel.  I will try this.

[KHorseman]

Before you remove duplicates, change the case to upper or lower for the whole column (assuming these "numbers" are actually text value alphanumeric codes rather than literal whole number values).

[Greg_Deckler]

Many times this is caused by capitalization issues.

[KHorseman]

Drop your NBO PNs Claimed[Parts Number] column onto a table visual, then add this measure:

Duplicate Test = COUNTROWS(NBO PNs Claimed[Parts Number])

Sort the table visual in descending numeric order by that measure and you will find something with a result of at least 2.

[emma313823]

Hi - trying to use your suggestions, however it shows the following...any thoughts?  Can't sseem to get it to work

[emma313823]

sorry one more thing...I had to fix what you showed as PARTS NUMBER to Part Number - there was no 's' in part.

[KHorseman]

Delete the part that says "Measure = "

[emma313823]

so sorry for all the questions...I removed measure = and it now shows this...

[KHorseman · Accepted solution]

Oops, change COUNTROWS to COUNT.

[emma313823]

Perfect....thanks so much for your help.  Great trick for my toolbox!

Emma

[KHorseman]

Glad I could help. How many duplicates did you find?

[emma313823]

there were 4.  I'm curious...not sure if you know or not, but if I use the query editor to remove duplicates prior to pulling the data in, why are duplicates still showing?

[KHorseman]

Are these text values or real numbers?

[emma313823]

I have both source files set to text

[Greg_Deckler]

Add a Text.Upper to your import prior to your removal of duplicates.

You should also add that to your other table as well so that everything matches.

https://msdn.microsoft.com/en-us/library/mt260665.aspx

If you right-click the column in Query Editor you will get an option to Transform | UPPERCASE