filterpowerquerylogical-operators

Power Query: Multiple Criteria Filtering with Logical Operators


I have the following table in power query:

enter image description here

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


Solution

  • Try:

    1. Add a custom conditional column depending if Name contains the string you want.
    2. Sort on the date column Text Between Delimiters DESCENDING
    3. Buffer the table to maintain the sort order.
    4. 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:

    1. GroupBy on the category column while keeping all Rows.
    2. Transform the nested table by sorting it then taking the Top 3 rows.
    3. 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"