Forum Discussion
Power Query - Return true for each List value that is contained within a string in a text column
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
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
5 Replies
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
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 ForumPlease 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
