Filter a list by using another list in PowerQuery M code

Question

I have a list of column names. I want to filter the list, by using the items in another list. I want to return all column names that Contains any of the items in the myFilter List. If the Filter is text item I can make the filter, but I could not manage to go through each item and return a list.

ColumnNames= {"E ID", "W ID", "P Chemical", "XIC","YFE","ZIC"}
myFilter = {"W ID","IC"}

The result should be:

Result = {"W ID","P Chemical", "XIC", "ZIC"}

As IC is contained in Chemical, XIC and ZIC, and W ID is a full match.

I imagine it should be something like this:

List.Select(ColumnNames, each Text.Contains(_, each myFilter{_}))

Because it works with this

List.Select(ColumnNames,each Text.Contains(_,"W ID")) 

But I cannot get the right code, and I appreciate the help.

Answer 1

I would do it like this.

let
    columnNames= {"E ID", "W ID", "P Chemical", "XIC","YFE","ZIC"},
    #"Converted to Table" = Table.FromList(columnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "columnNames"}}),
    myFilter = {"W ID","IC"},
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "filter", each myFilter),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "filter"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "match", each if Text.Contains([columnNames], [filter], Comparer.OrdinalIgnoreCase) then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each [match] = true)[columnNames]
in
    #"Filtered Rows"