Forum Discussion
Multiple lists with phrases for keywords
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-p/1025528#M483660
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
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
10 Replies
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
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 for a starter can you provide sample data and desired output, please?
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.
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
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.
