Power BI is finding Duplicates when trying to make a relationship

[Amberr567]

I have a table that potentially gets some duplicates in it because I'm appending user data from 2 databases. I am trying to marry up the data with the manager ID to obtain the manager's name and job information. So, basically I copied the user table and intended to get a relationship between the user table and the copy based on the manager's user id.

But I used the "remove duplicates" option and it seems to be removing the duplicates entirely just fine. But when I go to set up the relationship with the data (between managers and users), I expect to see a 1 : many relationship with manager : user, but I am forced to use many : many with the error that there are duplicates in the manager table.

I went into the manager table in "transform data" and have used the "removed duplicates" literally 3 different times in 3 different places in my transformation steps:

Okay, whatever, let me check to see why there's a duplicate. Maybe the name of the user is different between the two rows? I don't know. So I ordered my user id column by appending order and scrolled down until saw the id of the user. There AREN'T any duplicate rows. 

I don't know how else to check this. Why in the world is this happening? There's no rows at the beginning that has a space at the beginning. 

For now, the many : many solution works fine and I should see minimal issues with it, but I am trying to understand and learn if there is something I'm doing wrong.

Thank you in advance

added edit: I ended up rather using a lookup value command. It seems to be the better option. I was having some performance issues. I don't think it's necessarily related to this specifically, but with this, plus I was connected to a third DB, I just ran into some serious crash-worthy performance issues, so I removed the third DB (I didn't really need it, it was a workaround for this issue) and did a lookup value command on the manager table. The manager table has to be separate because the user table filters out the managers.

This is totally still an issue for me as I'd like to understand, but know that my sanity is okay as I found a viable workaround 🙂

[v-xinruzhu-msft · Accepted solution]

Hi @Amberr567 

Based on your description, before removing duplicate rows, you can add a step that replace value.

You can refer to the following sample, in this case I add a space at the end of value "abc",but it will not display when you view it.

So when i select remove duplicate rows, it still displays, so add a step before removing duplicate rows.

Then remove the duplicate rows can work.

You can also refer to the following M code in advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVorVAdMKYIahkTGYNjE1U4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ","",Replacer.ReplaceText,{"Column1"}),
    #"Removed Duplicates" = Table.Distinct(#"Replaced Value")
in
    #"Removed Duplicates"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Amberr567]

Because I put that workaround in place, I didn't end up trying this at the time of you posting it. BUT, I ran into the same issue today and there was NOT a workaround for it. So I gave your replace solution a try and it worked like a charm! Crazy how that worked out! Thank you!