Forum Discussion
Solved
Removing duplicates based on a criteria
Hi, how can I remove a duplicate based on a criteria in power query. For example, below, I would want to delete row 2 because there are ID 1111 and row 2 says "yes" under replaced. Thanks!
- waiting for the clarifications on the possible scenarios, ...a more robust solution
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRystXitWBcSpTi8E8IyMjhBQKx9jYGJ0D04QiZWJiguCYmprC1MUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, replaced = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"replaced", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {"repl", each if List.Count(List.Distinct(_[replaced]))>1 then Table.SelectRows(_,each [replaced]="yes") else _ }), #"Expanded repl" = Table.ExpandTableColumn(#"Grouped Rows", "repl", {"replaced"}, {"repl.replaced"}) in #"Expanded repl"
3 Replies
this code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRystXitWBcSpTi8E8IyMjhBQKx9jYGJ0D04QiZWJiguCYmprC1MUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, replaced = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"replaced", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {"repl", each if Table.RowCount(_)>1 then Table.SelectRows(_,each [replaced]="no") else _ }), #"Expanded repl" = Table.ExpandTableColumn(#"Grouped Rows", "repl", {"replaced"}, {"repl.replaced"}) in #"Expanded repl"change thi table
to this table
does the case of multiple rows with same ID and all replaced=yes exist?
what would be in this case the expected output
- waiting for the clarifications on the possible scenarios, ...a more robust solution
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRystXitWBcSpTi8E8IyMjhBQKx9jYGJ0D04QiZWJiguCYmprC1MUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, replaced = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"replaced", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {"repl", each if List.Count(List.Distinct(_[replaced]))>1 then Table.SelectRows(_,each [replaced]="yes") else _ }), #"Expanded repl" = Table.ExpandTableColumn(#"Grouped Rows", "repl", {"replaced"}, {"repl.replaced"}) in #"Expanded repl"Here is another approach. I used Anonymous M code to get the example data. His approach is more elegant, and may be more performant, but here it is just in case.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRystXitWBcSpTi8E8IyMjhBQKx9jYGJ0D04QiZWJiguCYmprC1MUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, replaced = _t]), merge1 = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"replaced", type text}}), #"Filtered Rows" = Table.SelectRows(merge1, each ([replaced] = "yes")), merge2 = Table.AddColumn(#"Filtered Rows", "Custom", each "no", type text), #"Merged Queries" = Table.NestedJoin(merge1, {"ID", "replaced"}, merge2, {"ID", "Custom"}, "merge2", JoinKind.LeftOuter), #"Added Custom" = Table.AddColumn(#"Merged Queries", "Rows", each Table.RowCount([merge2])), #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Rows] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"merge2", "Rows"}) in #"Removed Columns"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
