Forum Discussion
Anonymous
4 years agoNot applicable
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...
- 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
mahoneypat
Microsoft Employee
4 years agoHere'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
4 years agoNot applicable
Thanks Pat, I will give this a shot!