Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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