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.
- Anonymous4 years agoNot applicable
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.