Removing duplicates based on a criteria

[PowerBI123456]

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!

[Anonymous]

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

[Anonymous · Accepted solution]

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"

[mahoneypat]

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