Power Query: Identifying and removing duplicates based on column criteria

Hi 08152023 

You can create a blank query and put the following code to advanced editor in power query as an sample.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFMIzs0syVDSUQouSSwqSU0Bsgx13VKTlGJ1cCowwq7AOT+3ICcVosQYhxmlSUAWRIkJXElwYk5OpYJXfl5qMQ6H4FKBcEkyDp3JeNRjdzC6DNSdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Applicant Name" = _t, #"Application Status" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Applicant Name", type text}, {"Application Status", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Applicant Name", "Application Status"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index"}, {"Date", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each let a=List.Max(Table.SelectRows(#"Expanded Count",(x)=>x[Applicant Name]=[Applicant Name])[Date]),
b=Table.RowCount(Table.SelectRows(#"Expanded Count",(x)=>x[Application Status]="Completed" and x[Applicant Name]=[Applicant Name])),
c=if (b>0 and [Date]=a) or (b=0 and [Index]=1) then 1 else 0
in c),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.