topic Re: Relationships Issue in Desktop

Relationships Issue

emma313823 — Mon, 17 Jul 2017 13:31:47 GMT

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?

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 13:46:56 GMT

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.

Re: Relationships Issue

Greg_Deckler — Mon, 17 Jul 2017 13:49:22 GMT

Many times this is caused by capitalization issues.

Re: Relationships Issue

vanessafvg — Mon, 17 Jul 2017 13:52:34 GMT

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

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 14:05:01 GMT

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.

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 14:09:10 GMT

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.

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 14:10:42 GMT

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

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 14:11:50 GMT

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).

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 15:20:29 GMT

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

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 15:21:25 GMT

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

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 15:39:27 GMT

Delete the part that says "Measure = "

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 15:43:34 GMT

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

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 15:44:51 GMT

Oops, change COUNTROWS to COUNT.

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 15:49:41 GMT

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

Emma

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 15:51:00 GMT

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

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 17:25:20 GMT

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?

Re: Relationships Issue

Anonymous — Mon, 17 Jul 2017 17:40:04 GMT

Are these text values or real numbers?

Re: Relationships Issue

emma313823 — Mon, 17 Jul 2017 19:21:41 GMT

I have both source files set to text

Re: Relationships Issue

Greg_Deckler — Mon, 17 Jul 2017 19:25:31 GMT

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