Filter a list by using another list in PowerQuery M code

Question

List of column names that I have. I want to use the elements from another list to filter the current list. Any column names that contain any of the items in the myFilter List should be returned, if possible. I can create the filter if the filter is a text item, but I was unable to walk through each item and produce 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. Can someone please help me with this?

Answer 1

Try 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"