Forum Discussion
Power Query not removing all duplicates
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 ".
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"