M Remove duplicates not working

[Fblom]

The error I get is I have a master table with unique keys, which then I use to build relationships in the model to other tables.

The last step ogf the master table query is a remove duplicates from the key (ID) column. and still when refreshin the PBI I get an error that there are duplicates in my key and that is not allowed for 1 to many relationships.

I spotted the IDs showing duplicates in DAX, Indeed I can see 2 rows when I duplicate the query in a blank PBI w/o model relationships, but when checking those IDs in the query editor, there are no duplicates. 

I dont understand how is that I dont have duplicates in the editor but i get them in dax.. This data and powerbi have not changed and it always worked well till today. 

query:
let
Source = XXXX,
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Id"})
in
#"Removed Duplicates"

[v-pnaroju-msft · Accepted solution]

Thankyou @vojtechsima and @ronrsnfld for your responses.

Hi Fblom,

We appreciate your enquiry on the Microsoft Fabric Community Forum.

As mentioned by @vojtechsima and @ronrsnfld, this issue arises because Power Query evaluates duplicates in a case sensitive manner, whereas the Power BI data model treats text keys as case insensitive. Consequently, values such as ABC123 and abc123 are considered distinct in Power Query but are treated as duplicates in the model. In addition to the points already provided, hidden characters, leading or trailing spaces, or non breaking spaces may go undetected in the Query Editor and can result in duplicate key errors during model load.

Please follow the steps below, which may help to resolve the issue:

1. In Power Query, convert the key column to Text.
2. Apply the following cleanup functions before removing duplicates:

#"Cleaned" = Table.TransformColumns(Source, {{"Id", each Text.Lower(Text.Trim(Text.Clean(Text.Replace(_, Character.FromNumber(160), " ")))), type text}}),
#"Removed Duplicates" = Table.Distinct(#"Cleaned", {"Id"}, Comparer.OrdinalIgnoreCase)

3. Refresh the model and verify that no duplicates remain.

If the issue persists, check for hidden duplicate sources or for duplicates that may have been reintroduced after merges or incremental refresh steps.

We hope that the information provided will assist in resolving the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[Fblom]

Adding "Comparer.OrdinalIgnoreCase" to the remove duplicates has made the trick. 

My ID 99.9% of the times is only numbers, but in few instances has text (all capiltal leters).

The weird thing though was that in the query editor, if filtering one of the IDs showing this issue, after remove duplicates there was only 1 row. but after applying the change in DAX tables there were 2 rows..

Anyway, it sovled now. Thank you so much 🙂

[ronrsnfld]

We really need to be able to duplicate your problem to best help. Otherwise we are just guessing.

For example, if the problem is the different case of the lines, then you can use the optional comparer argument of the Table.Distinct method:

#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Id",Comparer.OrdinalIgnoreCase})

But there could be other issues also.

[vojtechsima]

hey. @Fblom ,

M and DAX evaluate duplicates differently; mostly they differ in how they are Caps senstitive. I recommend making the column ID lowercase and then removing duplicates.

I assumed it's a text, because for numbers that shouldn't happen at all..