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.

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 HunterUnderwater Basket Weaver 1Executive Assistant Barista 1Sarcasm 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.

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.

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

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.

Removing Duplicated to create a relationship!!!

14 Replies

Anonymous
Not applicable
10 years ago
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
  Regular Visitor
  10 years ago
  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
    10 years ago
    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.

Forum Discussion

Removing Duplicated to create a relationship!!!

14 Replies

Related content

remove duplicate value within a row

Removing Duplicates

Can't create relationship due to duplicate values (even after removing duplicates in Power Query)

How to stop PowerBI from automatically removing duplicates.

Removing duplicates