Power Query not removing all duplicates

[andrew_sxs]

Hi, first time using this so lets see how it goes...

I am working ith a file of nearly 1mil rows and 14 columns.

I am trying to remove duplicates within a column (LOCAL_ITEM_DESCRIPTOR) column.

I have therfore perfomed 'remove duplicates' function on the column.

However, when the data set updates, there are still 9 duplicates (see below in table).

I don't understand why these duplicates are not being removed. How can I get them removed through the remove duplicate function or with a secondary remove duplicates operation?

Can someone please help?

[Daryl-Lynch-Bzy]

Yes, this correct.  The function will remove the duplicate after retaining the row associated with the first unique instance from the column.  Here is an example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjRQitVB40RERgE5RlBOJYhjjKzMFM5RAPLMTMG80rzMwtJUsCFA6VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column For Distinct" = _t, #"Another Column" = _t]),
    #"Removed Duplicates" = Table.Distinct(Source, {"Column For Distinct"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Text.Length([Column For Distinct]))
in
    #"Added Custom"

 This example contains Data Qaulity exceptions so I may not have the desired result in Power Query.  I wanted XYZ to be unique, but XYZ and XyZ in Power Query terms are two distinct items.

When you get to DAX it will treat XyZ and XYZ has equals because it is not Case Sensitive like Power Query.  I think you need to Trim and Capitalise before using Distinct.  Like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjRQitVB40RERgE5RlBOJYhjjKzMFM5RAPLMTMG80rzMwtJUsCFA6VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column For Distinct" = _t, #"Another Column" = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Column For Distinct", Text.Trim, type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Trimmed Text",{{"Column For Distinct", Text.Upper, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Uppercased Text", {"Column For Distinct"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each Text.Length([Column For Distinct]))
in
    #"Added Custom"

[Daryl-Lynch-Bzy · Accepted solution]

Hi @andrew_sxs  - I would like to suggest that you Filter you population in Power Query before the remove Duplicates step.  If possible include an example where there are:

• no duplicates to remove
• duplicate has been removed successfully
• duplicate is not removed

This will help you visible see what is happening.  It is possible that you need Trim or change case on the column with the duplicates because the following are not the same:  "ABC" <> "AbC" or "ABC ".

[andrew_sxs]

trim function before remove duplicate worked... thanks 🙂

[AlexisOlson]

Did you remove duplicates on the whole table or only on a single column? The reason I ask is that it looks like the rows are probably unique even if there are duplicates in the first column.

What does the M code for the remove duplicates step look like?

[andrew_sxs]

M code is as follows - So I presume it is meant to be removing all duplicates within this column, despite the fact that other columns have differing information which would make the whole row distinct but not the column?

Remaining duplicate values (9 in total) in this column are as follows:

[wdx223_Daniel]

why did you distinct the table depending on the column of description, and then count the distinct values of item code?

maybe there some item codes have two descriptions.