Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

Filter a list by any matching string from another list

Hello,

I have a table with certain columns names, and I would like to remove some containing a defined string.

 

An example below where I only used a string "budget" as I am not sure how to do it with the whole list (ListTextToBeRemoved) as an example....

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2023 Plan" = _t, #"2024 Plan" = _t, #"Planned budget" = _t, #"Internal budget" = _t, #"Jan Forecast" = _t, #"Feb Forecast" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"2023 Plan", type text}, {"2024 Plan", type text}, {"Planned budget", type text}, {"Internal budget", type text}, {"Jan Forecast", type text}, {"Feb Forecast", type text}}),
ListTextToBeRemoved={"Plan","budget"},
ColumnstoBeRemoved=List.FindText(Table.ColumnNames( #"Changed Type"), "budget")
in
ColumnstoBeRemoved

 

Thanks for your help!

Kind regards

Valeria

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, ValeriaBreve I made it case insensitive. If you don't want that - remove comparer. 

    let
    ListTextToBeRemoved={"Plan","budget"},
    column_names = {"2023 plan", "2023 budget", "2024 fact", "2023 fact", "222 BUDGET"},
    ColumnstoBeRemoved = 
        List.Select(
            column_names, 
            each 
                List.ContainsAny(
                    {_}, 
                    ListTextToBeRemoved, 
                    (x, y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)))
in
    ColumnstoBeRemoved

     

     

     

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ColumnstoBeRemoved=List.Select(Table.ColumnNames( #"Changed Type"), each List.Contains(ListTextToBeRemoved,_,(x,y)=>Text.Contains(y,x)))

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    Hello, ValeriaBreve I made it case insensitive. If you don't want that - remove comparer. 

    let
    ListTextToBeRemoved={"Plan","budget"},
    column_names = {"2023 plan", "2023 budget", "2024 fact", "2023 fact", "222 BUDGET"},
    ColumnstoBeRemoved = 
        List.Select(
            column_names, 
            each 
                List.ContainsAny(
                    {_}, 
                    ListTextToBeRemoved, 
                    (x, y) => Text.Contains(x, y, Comparer.OrdinalIgnoreCase)))
in
    ColumnstoBeRemoved

     

     

     

    • ValeriaBreve's avatar
      ValeriaBreve
      Icon for Post Partisan rankPost Partisan
      3 years ago

      AlienSx wdx223_Daniel Hello, thank you SO MUCH! I am still not mastering functions 😞 I hope it will come one day by learning and doing....

      So just from a practical standpoint - I need to accept a solution only - I can't do for both - and you both replied so fast, thank you so much for this. I have decided to give the solution to AlienSx as there is the extra step of the case insensitivity - and I will give kudos to wdx223_Daniel - I hope this is OK!!!!!!

      Thanks again, Valeria