How to check if a column contains the characters in the column of another table

[Syndicate_Admin]

Hi, 

I have 2 tables below. I need to have a new column in Table1 to have a Boolean value if the Text contains any value listed in Table2 column "SearchText". I have tried to use list.ContainsAny() , but there is no space in the text so I can't use split to generate a text list. Is there any idea to achieve it? Thank!

Table1 

ID	Name
1	coffeeprod
2	ABCcigarette
3	XXalcoholXX
4	ddbeverages

Table2

SearchText

coffee

cigarette

alcohol

[Syndicate_Admin]

Thanks! it works for me. 

[AlienSx]

Hello, Jack7

    search = List.Buffer(Table2[SearchText]),
    contains = Table.AddColumn(
        Table1, "Contains", 
        (x) => List.MatchesAny(
            search, 
            (w) => Text.Contains(
                x[Name], w, Comparer.OrdinalIgnoreCase
            )
        )
    )

[Syndicate_Admin]

Hi @Jack7 ,

I assume that if value in Name column returns "true", otherwise, returns "false".

Please try this:

AddColumns(Table1,"test",!IsBlank(LookUp(ForAll(Table2.SearchText As C,C.SearchText in Name),Value=true)))

Best regards,

Rimmon