Conditionally Remove Duplicate Rows

[Syndicate_Admin]

Hi

I need to conditionally remove rows from a dataset within PowerQuery 

Reference

Owner

Date

Department

I want to remove Duplicate References where Owner is "Joe Bloggs" Date = >31/12/2022 and department  = "Finance" 

So 

Reference	Owner	Date	Department
123	Joe Bloggs	01/02/2023	Customer Service
123	Joe Bloggs	01/02/2023	Finance

On the above. I'd want to remove the duplicate row with reference "123" where Owner = "Joe Bloggs" and Date >31/12/2022 and Department = "Finance"

This should then leave the customer Service Row within the dataset 

Is this possible to do, rather than manually doing it within the dataset?

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8spPVXDKyU9PLwZyDAz1DYz0jQzAMs6lxSX5ualFCsGpRWWZyalKsTqENbll5iXmgdTGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Owner = _t, Date = _t, Department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Owner", type text}, {"Date", type date}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference", "Owner"}, {{"Count", each _, type table [Reference=nullable number, Owner=nullable text, Date=nullable date, Department=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([Count],"Flag",each if [Department]="Finance" and [Date]>= #date(2022,12,31) then 1 else 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Department", "Flag"}, {"Date", "Department", "Flag"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Flag] = 0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Flag"})
in
    #"Removed Columns1"

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.

[AbhinavJoshi]

Hello @Syndicate_Admin. I used group by with conditional logic. See the following custom column code 
if [Department] = "Finance" then Table.Group(#"Changed Type", {"Reference", "Owner"}, {{"Date", each List.Max([Date]), type nullable date}, {"All", each _, type table [Reference=nullable number, Owner=nullable text, Date=nullable date, Department=nullable text]}}) else null
I am not sure what you would like to do if the condition is not met, so you can change the logic part there.

I hope it helps!