Forum Discussion
How can I choose which duplicates to remove?
Hello
I want to remove rows that have duplicates in Column1. But I want to choose which duplicates to remove, i.e. remove the duplicate where Column2 has a specific value or is null or any other criterion.
How can I do that?
I was thinking to use some sorting, but not sure if PowerBI removes duplicates in order.
Thanks!
Hi Anonymous ,
Try to create a query like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YEwjAzATCeYGJzhDGQYmiKYEHUuQKYxhOkKZJoAFcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), a = Table.AddColumn(#"Changed Type","count", each let Column1=[Column1] in Table.RowCount(Table.SelectRows(#"Changed Type",each Column1 = [Column1]))), #"Filtered Rows" = Table.SelectRows(a, each [count] = 1 or [Column2] = null or [Column2] = 15), #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Column1"}), #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"count"}) in #"Removed Columns"Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Please see this article. To get the desired sort order, you could add a custom column to return Y or N based on your Column 2 value, and then use that column to sort prior to the approach in this article (buffer with remove duplicates).
Remove Duplicates and Keep the Last Record with Power Query - Excelerator BI
Pat
Hi Anonymous ,
Try to create a query like this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YEwjAzATCeYGJzhDGQYmiKYEHUuQKYxhOkKZJoAFcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), a = Table.AddColumn(#"Changed Type","count", each let Column1=[Column1] in Table.RowCount(Table.SelectRows(#"Changed Type",each Column1 = [Column1]))), #"Filtered Rows" = Table.SelectRows(a, each [count] = 1 or [Column2] = null or [Column2] = 15), #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Column1"}), #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"count"}) in #"Removed Columns"Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
