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
- PhilipTreacy
Super User
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
- edhans
Community Champion
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 - mahoneypat
Microsoft Employee
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
