I have the following table in power query:
I'm trying to return the latest files for BOTH words "RAM" and "SoCal". In this case, the return files would be:
RR RAM 2024-07-08.xlsx
RR SoCal 2024-08-05.xlsx
How would I write a function to do this?
I tried:
= Table.SelectRows(GetDateFromFileName, let latest = List.Max(GetDateFromFileName[Text Between Delimiters]) in each [Text Between Delimiters] = latest and (Text.Contains([Name], "RAM") or Text.Contains([Name], "SoCal")))
But it did not work.
Thank you
Try:
Name
contains the string you want.Text Between Delimiters
DESCENDINGSimilar 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:
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"