Cannot create relationship because od duplicates - but there aren't any

[IanR]

Hi,

I am working with a connection to Dynamics CRM Online. I am trying to create a relationship between my leads table and a new query/table that I have created called UsersAndTeams. I get the error message below. But there are no dupliates in my UsersAndTeams table!

You can't create a relationship between these two columns because one of the columns must have unique values.

The relationship is between the name of the user or team in the UsersAndTeams table and the Owner in the leads table. I have sorted UsersAndTeams and scanned by eye and could find no duplicates. There are no nulls or blanks. I put the Names column from UsersAndTeams on the report surface as a table, exported to Excel and imported that into Access where I checked for duplicated using a group by with a count and with the Find Duplicates query wizard. Access could find no duplicates.

 I realise that joining on text fields is not ideal but there are only 68 records in UsersAndTeams. This table was created by appending the Name and Created On fields from the TeamSet Table to the FullName and Created On columns from the SystemUserSet table. The names that I see in this table match the names that I see in the Owner column of my leads table (which does contacin thousands of duplicates). 

How do I convince Power BI that there are no duplicates in my UsersAndTeams table so that it will let me create this relationship.

Thanks

Ian

[IanR]

There was a duplicate. I put a COUNTROWS measure into the table visual with the names and one of them came up with a count of two. I know I wear glasses but am I really that blind? I was searching for duplicates, by eye, in the Query Editor. Maybe that interface isn't the bast one - I know it only give a 'flavour' of what's in the data with large tables, perhaps it does that to small tables as well. Next time I will use the Data view if the main Power BI app.

[v-sihou-msft · Accepted solution]

@IanR

Next time if you get duplicates issue, you can create a calculated table using SUMMARIZECOLUMNS() function to remove your duplicate rows.

Table = SUMMARIZECOLUMNS(Table1[Column1],Table1[Column2],....)

Regards,