Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
0 | |
phone | 2 |
mfa | 1 |
clock | 1 |
text | 4 |
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.
Solved! Go to 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.
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.
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.
Hi @Anonymous ,
Please see if the solution helps.
Regards,
HarshRegards,
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |