Forum Discussion

adamsharma's avatar
adamsharma
Frequent Visitor
4 years ago
Solved

Find String Containing All Words in Another String

Hi there,   Driving myself to distraction trying to figure this out so would appreciate some help.   My situation is I have two tables which contain columns with strings. I want to take the value...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

This was an interesting one, so I played with it too. Here is another approach that seems to work. FYI that I converted both [string] columns to lowercase first (but you could add that into the expression too instead, if desired).

 

 

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}}),
  #"Lowercased Text" = Table.TransformColumns(#"Changed Type", {{"string", Text.Lower, type text}}),
  #"Added Custom" = Table.AddColumn(
    #"Lowercased Text",
    "Custom",
    each
      let
        thisphrase = [string],
        thislist = Text.Split(thisphrase, " "),
        othertablelist = Table2[string],
        selrows = List.Select(
          othertablelist,
          each Text.Contains(
            Text.Combine(List.Intersect({Text.Split(_, " "), thislist}), " "),
            thisphrase
          )
        ),
        result = if List.Count(selrows) > 0 then "Y" else "N"
      in
        result
  )
in
  #"Added Custom"

 

 

 

 

Pat

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    This is pretty clever. There are some cases where I think it will fail though. For example, "and this string" wouldn't match with "this string and this other string" since the concatenation of the intersection would be "this string and".

     

    I'm not positive but you might be able to resolve this by taking the intersection in the reverse order since it appears that the sort order of the first argument in List.Intersect is the one that is preserved.