Forum Discussion
Find String Containing All Words in Another String
I think I've got it. Try substituting in this expression:
if List.Count( List.Select( Table2[string], each List.Accumulate( Text.Split(Text.Lower(_), " "), Text.Split(Text.Lower(row[string]), " "), (s, c) => if s <> {} and c = s{0} then List.Skip(s) else s ) = {} ) ) > 0 then "Y" else "N"The logic here is that it iterates through the Table2 string word by word deleting the first word of the Table1 string if it matches that word. Any Table1 string that gets entirely deleted by this iterative process is one that has all the matches in the right order.
Super UserI haven't yet solved the case where order matters but you can use List.ContainsAll to check if all of the words in Table1[string] match a row in Table2[string].
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJLS5RKC4pysxLV4rViVYyAoo55uWXZKQWKZSgyRkD5SJTSxQSccibgMzLyCxGFjMFigWDuQolQCml2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, string = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"string", type text}}),
#"Added Custom" =
Table.AddColumn(
#"Changed Type", "Custom",
(row) =>
if List.Count(
List.Select(
Table2[string],
each List.ContainsAll(
Text.Split(Text.Lower(_), " "),
Text.Split(Text.Lower(row[string]), " ")
)
)
) > 0
then "Y"
else "N",
type text
)
in
#"Added Custom"Hi Alexis,
That is brilliant, thank you!! Even without the check for order of words, that gets me an awful lot closer to what I'm looking for. I will endeavour to learn more about the various List functions as it looks like they hold the key to resolving quite a few headaches I encounter.
Much appreciated!
- AlexisOlson
I think I've got it. Try substituting in this expression:
if List.Count( List.Select( Table2[string], each List.Accumulate( Text.Split(Text.Lower(_), " "), Text.Split(Text.Lower(row[string]), " "), (s, c) => if s <> {} and c = s{0} then List.Skip(s) else s ) = {} ) ) > 0 then "Y" else "N"The logic here is that it iterates through the Table2 string word by word deleting the first word of the Table1 string if it matches that word. Any Table1 string that gets entirely deleted by this iterative process is one that has all the matches in the right order.
Thanks Alexis, gave this a quick try just now and seems to work but lead to a "There weren't enough elements in the enumeration to complete the operation" error with certain combinations - I haven't had chance to have a proper look yet though, hopefully will later this week. Really appreciate your suggestions!
