Power Query - Return true for each List value that is contained within a string in a text column

[Anonymous]

Power Query - Return true for each List value that is contained within a string in a text column

I have a column "Text_Column" that has text strings. 

I have a list that contains keywords that are within the Text_Column

All I want to do is to return true (or 1) if any of the list values are found in the Text_Column FOR EACH row

This seems like a common scenario but I can't figure it out. 

I was trying to use List.Contains but it doesn't work. This is what I would have thought should work. 

Table.AddColumn(Source, "LIST_VALUE_FOUND", each if List.Contains(MyList, [Text_Column]) then 1 else 0

Is this possible?? Thank you

[Anonymous]

You can use:

Filtered=Table.AddColumn(LastStep, "NewColumnName", each Text.Contains([ColumnToFilter], List.Buffer(NameOfListWithStrings)))

[mahoneypat]

Please clarify if you are comparing lists on the same row.  Do both columns say "List" for each row?  If so, can use the List.ContainsAny function - List.ContainsAny([MyList], [Text_Column])

If they are actually text strings with a delimiter or if you are generating a list of all the rows in the Text_Column column, a different approach is needed.

Regards,

Pat

[Anonymous]

You can use:

Table.SelectRows(LastStep, each Text.Contains([ColumnToFilter], List.Buffer(NameOfListWithStrings)))

[edhans]

List.Contains is an exact match. Does the item you are checking against exist in the list as a separate item, not within the items. So if you have the word Hello you are looking for, if the list has "Hello World" it will not find it. 

Text.Contains will - as that i searching for a string within a string.

Can you post some data with what you are trying to accomplish and we can help come up with a solution.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Try this, works for me.  If you can't adapt it to your situation, please post some sample data I can use.

let
    TextCol = {"a big banana", "a small orange", "a green pear", "four red apples", "a pink flamingo"},
    MyList = {"apple", "banana", "pear"},
    Source =  Table.FromList(TextCol, null, {"Strings"}),
    #"Added Custom" = Table.AddColumn(Source, "Check", (C) => List.AnyTrue(List.Transform(MyList, each Text.Contains(C[Strings], _))))
in
    #"Added Custom"

Phil