Filter string column with list of strings

[Anonymous]

Hi guys,

I have a power query list which has a column [Products] containing strings. I want to filter the table with this column by using multiple strings (keywords) that are contained in a list (Table4).

I tried to set this up by creating a new check column, which I would then use to filter the table. However, the column will just read "function". Any help?

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),
    Checkpoints_Sheet = Source{[Item="Checkpoints",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Checkpoints_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Check", each (C) => List.AnyTrue(List.Transform(Table4, each Text.Contains(C[Products], _))))
in
    #"Added Custom"

[AlB · Accepted solution]

Hi @Anonymous 

Right now you are returning a function for each row. Drop the first each in your last step (the one in red below)

#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Check", each (C) => List.AnyTrue(List.Transform(Table4, each Text.Contains(C[Products], _))) )

By eliminating that each, the step will return the result of the List.AnyTrue( ... ). Do make sure Table4 is actually a list, since you're passing it as first argument fro the List.Transform( )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers