Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.