The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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.
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.
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!