Multiple lists with phrases for keywords

[Anonymous]

One question I havent been able to find the answer to - I'm doing a keyword search that parses the short description field for phrases categorized into a bunch of different lists.  Looks like I found the same code as this guy:
https://community.powerbi.com/t5/Desktop/Keyword-search-of-multiple-columns-with-multiple-keywords/m...
but unfortunately the method is only finding matches if the words in the list are single words.  This significantly limits the accuracy of matching.

Do Lists accept rows that are phrases, e.g. "Installation stuck" or "camera not detected" - for whatever reason it returns all null if I have multiple words in the row, and with single word entries, it matches ok but not great and I'm getting hits matching multiple lists.

Here's a sample of one of the functions....thanks!!!

(String) =>
let
//check if values in MyKeywords is in String
MatchFound = List.Transform(List.Buffer(#"MSTeams - Access-Permission"), each Text.Contains(String, _, Comparer.OrdinalIgnoreCase)),
Position = List.PositionOf(MatchFound, true),
//return category name if match
Return = if Position < 0 then "empty" else #"MSTeams - Access-Permission"{Position}
in
Return

[ronrsnfld]

Lists can contain almost anything, including strings that are made up of more than one word.  As @smpa01  requested, a sample of your data, keywords list, and desired output would go a long way in helping us help you craft a useful solution.

[Anonymous]

Thank you very much for answering the question about what's acceptable in a List!

Here's a sample List for "Teams-Desktop" lookups:

Add Contact

Delete Contact

Clear Cache

Conversation missing

Message missing

Credentials Error

add in

Delete message

Hide/Delete Chat

Icon missing in Desktop

Installation

slowness

Latency

Login Issue

Network Issue

Notifications

Profile Information Sync

Distort

Security Pop-up

Status Sync

Unable to launch application

Unable to make calls

Unable to receive calls

Unable to Send message

Unable to Receive message

Unable to View

Audio

Here is a sample of the description field that is getting parsed:

short_description

No audio on teams meeting

Audio is not working in the Teams meetings

Teams add in disabled

: Teams: camera not working

Assistance in changing the audio output setting

Bad connection on teams. Freezing video/ choppy audio.

Bottom half of the Teams app distorts in the middle of the meeting during screen share

call on teams disconnecting  - weak internet error

Calls keep dropping in teams

camera disabled in teams meeting

Can not turn on webcam on Teams (says "video sharing is disabled by the administrator")

Experiencing "Bad Network Quality" on all Microsoft Teams calls,

Teams - unable to present screen

Teams latency

Teams; Calendar is empty.

Unable to go on mute ( unable to mute or un mute on teams )

white bar on TEAMS

I had to do a little pruning for the data, thanks for your input and assistance

[ronrsnfld]

With data and lookups like that, you could try doing a fuzzy join, and adjusting the threshold until you get reasonable results.  How successful you are will probably depend on how critical you need to be of the outcome.

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below. You can reference your keywords list instead of the hard-coded one shown. 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45Wys9TSFTIyC8tTlWK1YlWKs8syQAK5MIFwPJJ+YklyNLpMD5Y1gnMiwUA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Phrase = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Phrase", type text}}),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Custom",
    each
      let
        keywords = {"boat", "mouse"},
        thisphrase = [Phrase],
        foundwords = List.Select(
          keywords,
          each Text.Contains(thisphrase, _, Comparer.OrdinalIgnoreCase)
        )
      in
        if List.Count(foundwords) > 0 then "Y" else "N"
  )
in
  #"Added Custom"

Pat

[Anonymous]

Hi, @mahoneypat,

this is a great and very neat solution. I was looking for something similar and your suggestion made the trick. After few minor adjustments it was exactly what I needed. You saved me a lot of sweat and headache.

[Anonymous]

Thanks Pat, I will give this a shot!

[smpa01]

@Anonymous  for a starter can you provide sample data and desired output, please?

[Anonymous]

The intent here is to categorize tickets that are in flight, since additional categorization is only done upon ticket resolution.  

An example of what would be in the ticket's short description can be something like "Microsoft Teams - calls are redirecting to voicemail directly" or "Unable to hear via headset on teams" and I need to match it with things in the Lists, e.g. "Redirecting to voicemail" or "unable to hear."  But so far it only works if my list entries are one word e.g "redirect" but as you can see, it gets tricky and inaccurate trying to match just a single word.  I need to match phrases.

Desired output at its simplest is just a hit on the List's name, identified in a custom column.  The Position syntax works, its just that I'm not getting any hits at all if my List has entries that are many words long and not just one word.

My first question here is regarding whether or not multiple words can be used in a List's row, e.g. "installation stuck" as detailed in my OP.  If they can be used, I'd love to figure out why what I'm doing doesnt work with them, but if its just a limitation of Lists then ok I can move on from here and try other methods.

[Anonymous]

I thought this would be a relatively simple question to answer, nobody knows whether a List can have multiple word entries or must a List have single worded entries?

[Anonymous]

Right, so I guess this one can be filed alongside How many licks does it take to get to the center of a tootsie roll tootsie pop...