Try:
- Add a custom conditional column depending if
Name contains the string you want.
- Sort on the date column
Text Between Delimiters DESCENDING
- Buffer the table to maintain the sort order.
- Remove duplicates on the conditional column
Similar to:
let
...
#"Added Conditional Column" = Table.AddColumn(GetDateFromFileName, "Custom", each if Text.Contains([Name], "RAM") then "RAM" else if Text.Contains([Name], "SoCal") then "SoCal" else "ignore"),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Text Between Delimiters", Order.Descending}}),
TableBuffer = Table.Buffer(#"Sorted Rows"),
#"Removed Duplicates" = Table.Distinct(TableBuffer, {"Custom"})
in
#"Removed Duplicates"
Follow-up to the ask in the comments about doing similar but keeping the top 3 of each category:
- GroupBy on the category column while keeping all Rows.
- Transform the nested table by sorting it then taking the Top 3 rows.
- Expand the nested table
Example:
let
...,
#"Added Conditional Column" = Table.AddColumn(GetDateFromFileName, "Custom", each if Text.Contains([Name], "RAM") then "RAM" else if Text.Contains([Name], "SoCal") then "SoCal" else "ignore"),
#"Grouped rows" = Table.Group(#"Added Conditional Column", {"Custom"}, {{"Rows", each _, type nullable table[Content = nullable text, Name = nullable text, #"Text Between Delimiters" = nullable Int64.Type, Custom = any]}}),
#"Sort and Take" = Table.TransformColumns(#"Grouped rows", {"Rows", each Table.FirstN(Table.Sort(_, {{"Text Between Delimiters", Order.Descending}}), 3) }),
#"Expanded Rows" = Table.ExpandTableColumn(#"Sort and Take", "Rows", {"Content", "Name", "Text Between Delimiters"}, {"Content", "Name", "Text Between Delimiters"})
in
#"Expanded Rows"
