Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Syndicate_Admin
Administrator
Administrator

Conditionally Remove Duplicate Rows

Hi

 

I need to conditionally remove rows from a dataset within PowerQuery 

ReferenceOwnerDateDepartment

 

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

 

So 

ReferenceOwnerDateDepartment
123Joe Bloggs01/02/2023Customer Service
123Joe Bloggs01/02/2023Finance

 

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

vxinruzhumsft_1-1695284245362.png

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.

 

 

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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"

vxinruzhumsft_1-1695284245362.png

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
Responsive Resident
Responsive Resident

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.