Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Indexing keywords found in emails

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

WordCount
email0
phone2
mfa1
clock1
text4

Email

EmailIDEmailText
1Various text
2Text about phones
3Text about clocks
4Text about mfa
5My 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:

 

WordEmailID
phone2
phone5
clock3
mfa3
text1
text2
text3
text4

 

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.

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

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"

 

5.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

3 REPLIES 3
v-diye-msft
Community Support
Community Support

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"

 

5.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

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
Community Champion
Community Champion

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)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.