Forum Discussion
Removing Duplicated to create a relationship!!!
If you're trying to create a relationship between two tables, the matching columns that you're relating has to have unique values in at least one of the two tables. You haven't given any specifics about the tables you're trying to relate, so it's hard to give any advice beyond that.
I have a table that has names and job titles, some names have the same job titles.
I than have another table which is the job titles and salaries, with no duplicate job titles. Therefore this list should be the unique one. Once I load into Powerbi, it says there are 39 rows 37 distinct value. Applying all the things in the original post does not make this list a unique set. What am I doing wrong...
- kcantor
Community Champion
You must have a 'close' match. I would recommend (with such a small dataset) that you simply use the drop down arror for the column and scroll down it to see if you have any matches without capitol letters or spaces. I know you shouldn't have to and I know it is tedious but the last time this happened to me, it was picking up a couple of blank rows and another time, the words matched but the case did not.
Nice, I didn't even think of "Job Title" vs "Job title". That's probably more likely than my guess.
I simplified the numbers for explanation sake. I have 7,904 rows and 7,902 distinct :(
Go to your query and select that job title column. Go to the Transform Tab and hit the Format menu (middle of the ribbon, near Split Column), and select Capitalize Each Word. Then remove duplicates. If kcantor is right that will probably catch them. You may also have to pick Trim from the same menu if the problem is an extra space character.
Sort the job titles in alphabetic order and look for whatever it's showing as duplicates. If there are only 37 unique values you definitely have duplicates even if you don't expect them. How many actual job titles are in the source file? If it's 39 then you must have duplicates in the source file without realizing it. if it's only 37 then somehow you have duplicated a couple of rows in your query, in which case I'd say let's see your query code from the advanced editor (sanitized as necessary for security purposes of course).
My guess is that you have something like the same job title in two different departments with two different salaries. Removing duplicate rows from the table would leave those because the combination of Job Title X and Salary Y is different from Job Title X and Salary Z.
