Indexing keywords found in emails

[Anonymous]

I have a table that contains plain text from emails that I need to search for a specific list of keywords and then return a list of the emails that contain those words and then do reporting on them. There are about 500k emails and 30 odd words to look for. I can search for and count the emails containing the words with a calculated column, but this does not allow me to filter the list of emails by the words. My current tables look like this:

Word

Word	Count
email	0
phone	2
mfa	1
clock	1
text	4

Email

EmailID	EmailText
1	Various text
2	Text about phones
3	Text about clocks
4	Text about mfa
5	My phone is broken 😞

Doing the count takes FOREVER and I'm not able to filter the emails by the words in a visual afterwards. I though that an index table like this would do the job and make it a bit quicker:

Word	EmailID
phone	2
phone	5
clock	3
mfa	3
text	1
text	2
text	3
text	4

But I can't figure out how to create this from the existing tables. Is anyone able to help, either with creating this table or a smarter suggestion on how I can do this? Thanks for any help.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

You can use the M query, kindly find my below results:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpLLMrMLy1WKEmtKFGK1YlWMgIKhgA5ColJ+aUlCgUZ+XmpxWAZY1SZ5Jz85GyIjAmqTG5aIljYFCjsWwkxQiGzWCGpKD87NU8pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EmailID = _t, EmailText = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EmailID", Int64.Type}, {"EmailText", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Word[Word]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Inserted Lowercased Text" = Table.AddColumn(#"Expanded Custom", "lowercase", each Text.Lower([EmailText]), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Lowercased Text", "Custom.1", each if Text.Contains([lowercase], [Custom]) then [EmailID] else null)
in
    #"Added Conditional Column"

Pbix attached.

[Anonymous]

Thank you, this was a big help. However, anyone else who is trying this should be careful to remove blanks and nulls from their equivalent of EmailID column or you will get errors.

[harshnathani]

Hi @Anonymous ,

Please see if the solution helps.

https://community.powerbi.com/t5/Desktop/Keyword-search-of-multiple-columns-with-multiple-keywords/m-p/1025528

Regards,

HarshRegards,
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)