M Remove duplicates not working

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.