Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ValeriaBreve
Post Patron
Post Patron

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

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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

 

 

 

View solution in original post

3 REPLIES 3
AlienSx
Super User
Super User

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

 

 

 

@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

wdx223_Daniel
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.