Removing Duplicated to create a relationship!!!

[keyahorm]

I am trying to create a relationship between 2 tables. and for all my might and anger I cannot get Powerbi to date the dataset, which I already removed duplicates for in Excel pre load to filter out the duplicates. Always xxx rows xxx -2 unqiue values!

In the query editor I have removed duplicates, removed blank rows, removed errors, Filtered> removed empty. NOTHING, very frustrating.

[KHorseman]

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.

[keyahorm]

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]

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.

[keyahorm]

I simplified the numbers for explanation sake. I have 7,904 rows and 7,902 distinct 😞

[KHorseman]

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.

[KHorseman]

Side question: holy moly, does everybody in this company have their own unique job title?

[keyahorm]

Hahaha seems so ! Thanks for the help thus far both. 

Transform trick did not work... 😞

I can't understand how following these steps I don't get a unique list:

1. Duplicate original table in PowerBI query editor. 

2. Delete all but job titles columns

3. Apply every kind of removed duplicates, removed blank rows, removed errors, Filtered> removed empty.

What else can I do?

[KHorseman]

You did both Capitalize and Trim? OK, we're going to have to hunt this down by hand. Load your query and go into the report editor. Write a measure (with whatever names you called things, obviously):

Duplicate Hunter = COUNTA(SalaryTable[JobTitle])

Now add a table visual with SalaryTable[JobTitle] and the measure [Duplicate Hunter]. That should give you something that looks like:

Job Title                                    Duplicate Hunter

Underwater Basket Weaver       1

Executive Assistant Barista        1

Sarcasm Translator                   1

...

Somewhere in that list there's either a pair of titles with a 2, or one title with a 3. Go to filters and set it to Duplicate Hunter is greater than 1. Whatever is left are the duplicates. Hopefully seeing what it is will give you a clue as to how to fix it. If not...uh...we can talk about that when it happens.

[kcantor]

@KHorseman That is a great idea . . . going to hijack this particular reply and ask if you can help me solve my many to many issue: http://community.powerbi.com/t5/Desktop/Is-this-Possible-DAX-many-to-many/m-p/37128#U37128

[KHorseman]

@kcantor after an hour of thinking about your question I started to smell burning circuitry so I'm going to give my brain a break. But if you could send me your sample data maybe I can do better with something concrete.

@keyahorm I'm dying of suspense. What's the word? Did that help you track down the culprit?

[leinad13]

This seems to be a common problem for me, I wonder Microsoft choose not to display the non duplicates?

The error "One of the columns must have unique value" is particularly frustrating, especially after you have chosen 'Remove Duplicates' on both columns. I understand there may be technical reasons for this - but from a usage point of view. The error is useless - the computer may as well just beep loudly, would give you same information as the error!

[ScottFerguson]

If this is still open, I have found removing duplicates in a workskheet in the source file (usually excel) is easier. Then you can load the table to creat the relationship in Power BI.

[KHorseman]

Nice, I didn't even think of "Job Title" vs "Job title". That's probably more likely than my guess.

[KHorseman]

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.