remove duplicates based on certain criteria

Hi Anonymous ,

We can add a step using Table.SelectRows function, please change #"Changed Type" to the name of your previous step.

RemoveDuplicate = Table.SelectRows(#"Changed Type",each let s=[Source] ,i = [ID] ,  totalSameIDNumber = Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]=i)) in totalSameIDNumber=1 or s="pipeline")

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUXJ0ctYDUuWZeUDS1MAASLoFAYnEpGS95PxcIKsgsyA1JzMvVSlWJ1rJGCgQEREBJBWgGKy6oqICqjozryQxG6LWEMj19fVFUovAIHmo9ci2ww1UQDXKCOIiqIQh2JkKKK5EUoxwY05+cQlcvZ8PVBPCQ7EA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Client = _t, Stage = _t, Fee = _t, Country = _t, url = _t, Source = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Client", type text}, {"Stage", type text}, {"Fee", Int64.Type}, {"Country", type text}, {"url", type text}, {"Source", type text}}),
    RemoveDuplicate = Table.SelectRows(#"Changed Type",each let s=[Source] ,i = [ID] ,  totalSameIDNumber = Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]=i)) in totalSameIDNumber=1 or s="pipeline")
in
    RemoveDuplicate

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

Best regards,