Filter column based on two rules

[Vialle_Diego]

I need to filter the "Case" column based on the most recent date in the "Opened on" column, and keeping the information in the "Assignment Group" column. The "Case" column has duplicate values.

@tamerj1 

[Anonymous · Accepted solution]

Hi @Vialle_Diego ,

Please refer to this step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc9LDoAgDEXRvXRMUiigY1A+UXdg2P82xMEzddaXM7jpfZOz1pEhx47FiswzJRrmA2EB5KzBswdsG+BdgQNg3zVEjoBSAP7fqFXDwgugNUD4N3oHxLlWXgHXpUE1jkOD+uM8aYwH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Case = _t, #"Opened on" = _t, #"Assignment Group" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case", Int64.Type}, {"Opened on", type date}, {"Assignment Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Case"}, {{"Data", each _, type table [Case=nullable number, Opened on=nullable date, Assignment Group=nullable text]}, {"Max Date", each List.Max([Opened on]), type nullable date}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Opened on", "Assignment Group"}, {"Opened on", "Assignment Group"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Data", "Custom", each if [Opened on] = [Max Date] then 1 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Date", "Custom"})
in
    #"Removed Columns"

Original table:

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data