Thursday, April 12, 2018

Re: Can´t create a relationship between two columns because one of the columns must have unique valu

 encountered this issue when trying to establish the relationship between two tables, however when I used group by statement to find the duplication, nothing turned up.

I then imported these two tables into Power BI and use the countrows and distinctcount which revealed that there are indeed duplicated rows.

I then created a calculated column as below and put it into a grid , filter the RecordCount>1

RecordCount = COUNTROWS(filter(ALL('dw DmResources'), 'dw DmResources'[ResourceID]=EARLIER('dw DmResources'[ResourceID])) )

the above step showed what's duplicated.

I did a select with like statement in SQL to find the duplicated records, copy and paste to Excel and compare the two duplicated string char by char and Excel told me that the different was a space CHAR(32)

the funny thing started here, I used LTRIM in sql to remove the trailing space, I also tried repalce(xx, char(32),'') , no luck.

At the end I use the ASCII(SUBSTRING(xxx, m, k)) in the sql query and it told me that the difference is actually a TAB (CHAR(9) ), apparently when copying from SQL output to Excel, the process automatically convert the TAB to a space???  maybe it is due to the fact that  in Excel you can't enter a tab because it is a reserved function key?

Problem finally solved after all these drama.