Forum Discussion
Multiple lists with phrases for keywords
- 4 years ago
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
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.
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
- ronrsnfld4 years ago
Super User
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.