Forum Discussion
Extract external email from cell
Hello,
is it possible to extract data from a cell but dont know wxactly what it will be?
I would like to extract external email address such as [email protected]. (there could be our intrenal email/domain in the cell also.
In a CELL it could contain data such as the below block of info;
Also this one as well please Thank You Regards Internal company info removed From: Postmaster <postmaster@Mydomain> Sent: 26 July 2021 14:40 To: first last <[email protected]> Subject: Files attached to a message triggered a policy Files attached to a message triggered a policy Contact your administrator if you need these files. Message Details From "Brian Quinn" <[email protected]> To first last <[email protected]> Subject [EXTERNAL] RE: Old Defined Benefit Scheme Date Mon, 26 Jul 2021 13:39:37 +0000 Policy Default Attachment Management Definition - Block Dangerous File Types Status The message has been placed on HOLD - action required File Details Attachment Policy (Default Attachment Management Definition - Block Dangerous File Types) Attachment Name: 201409.zip Policy Name: Default Attachment Management Definition - Block Dangerous File Types Detected as: zip Size: 133278 bytes Action Taken: HOLD (Entire Message Held for Review) Reason: Encrypted Archive Detected © 2003 - 2019 Mimecast Services Limited. 563
Hi CraigDykes1 ,
Ah okay, now I get you.
Try this on blank query.
we can still imporve the query but it achieves your desired output.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VbBbtpAEP2VEadGTS0bAwk+hQRQ0sZJCkhtlOSwmAG2rHfp7jqEHPs3/Y32xzprG0KbSG2qpFWlcoDVY/1m5s3sW19cVFrCKLBTbkBJBGZggULAXCAzCIMpkzM4Vxn0cML0yMCRtKglE5CodM7kErgcK9CYqmscQVerNIIzZWzKDG2Ey8z3w2S+Bvbi5UiljMv8D4Q+ShtBtQGvM7GEql8NIKhFNR8GKoIx18aCoAdLnq4DPAeseTzKY8WVDT9gQnRdLtAAs5YlU0rKKmCQojFsgmA1n0xQE8xgrgRPlo/dfqAk7bSwVJkGNkq55MZqZpUGPnYoSHQ0UyQBx47bg7ika6NlXJhcJ7is7GvOJLzNuJSXlbLGocO8jw7b6yqNfCLbP1Y6UL+pDVx03g86vZPW8RX0OhGcihHlNOaSEt5HSSsLfVIhpVSZRYiV3C67UzYnjMJmFO7AS58+1OlcEqJgmbDQyjVMqacQM0kF58s8ALdcSXgF+0IlMyKXpKrKTC4+DJZz6kDfMkvIYIpr+ac0j0NESfPIEsqRKA5Pj9vEQx1whBpJKdeenGcl70YeZYYvniTFrc3HT1iKNLp+UPOb3i2fr0IV+NNIQgVR19z0mQhcjD6/JfIgDKs7uzBcWtrTKpQYsBnKqJDnRUdaUmU9dodIfR7TgPbwmuNii36ZUbS7IxO9nLsALZ1M+TXeRfz66ctnqs4PKUMqsgkxTzFx89ZHfc0TinzMU05bPag3wsrV9kXlkMMBDbQ7AxoLB6FjQJGFUAsuJ4A0litz8R5wlzeoOX2hJJZf8ZJ7J6PwkzAgsVd20oiC3dxONskLrhkh3ixHHiT75wxlo8S1ofykSPKS7lGvPzhpxR04bhWL0lJWuLfCg3ptb2h5fgegfcBfuu8iiM/bp3Hr6ATOBCndI1tZsNI+7kzluxbVo2Anqjb/m0ppHptWPMpoI10ENCbFhesk8+YL70aYm2f3nDxIYTrVoO7XntRz2irJytTKkPeUiFFPCDfUxbwKsy5/pJLnLz8PUpRfa4Z+vfGn68/Px+oIHdA7lgaB1rmhoy7fwf6GHNVqYzd4bjkecQdVrq6+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Column1 - Copy", Splitter.SplitTextByDelimiter("<", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1 - Copy"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Column1 - Copy", each Text.BeforeDelimiter(_, ">"), type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter",{{"Column1", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Text.Contains([#"Column1 - Copy"], "@") and not Text.Contains(Text.Upper([#"Column1 - Copy"]), "MYDOMAIN" )) in #"Filtered Rows"Hope this helps
8 Replies
CraigDykes1 , You need a split column on From, then text Between < > first occurrence
like
Text.BetweenDelimiters(List.Last(List.FirstN(Text.Split([Column], "From "),2)), "<", ">")
Hello, thanks for your quick reply.
The external domain may not always be the first occurance of an email in the cell.
Is it possible to extract data from a cell between "<" and ">" if it doesnt contain @mydomain.com ?
CraigDykes1 One way:
Column = VAR __ForeignDomain = "@ForeignDomain.com" VAR __MaxEmail = 50 VAR __Location = SEARCH(__ForeignDomain,[Column1],,0) VAR __Location2 = SEARCH("<",[Column1],__Location-50,0) VAR __Location3 = SEARCH(">",[Column1],__Location,0) RETURN MID([Column1],__Location2+1,__Location3-__Location2-1)A perhaps more elegant way would be to use Text to Table: Text to Table - Microsoft Power BI Community
Hi CraigDykes1 ,
would this work on yours?
I used your sample text and used this:
Text.BetweenDelimiters( Text.BetweenDelimiters( [Column1], """", ">" ), "<", ">" )If you have other scenarios,
let us know.
By the way, this is done in Power Query. Not in DAX.
Hope this helps.
mussaenda
Thats a huge improvement.If im being picky..Some of the data is still appearing with internal emails.
(Result is [email protected])
Hi Can you release the following email please. Thank You Regards Keri Keenan From: Postmaster <[email protected]> Sent: 31 May 2021 16:18 To: Keri Keenan <[email protected]> Subject: Files attached to a message triggered a policy Files attached to a message triggered a policy Contact your administrator if you need these files. Message Details From "Keri Keenan" <[email protected]> To FIRSTNAME LASTNAME<[email protected]> Subject FW: MYDOMAIN Plan Renewal 2021 Date Mon, 31 May 2021 15:17:29 +0000 Policy Default Attachment Management Definition - Block Dangerous File Types Status The message has been placed on HOLD - action required File Details Attachment Policy (Default Attachment Management Definition - Block Dangerous File Types) Attachment Name: Benefit Schedule For Company 2021.pw.xlsx Policy Name: Default Attachment Management Definition - Block Dangerous File Types Detected as: xlsx Size: 21504 bytes Action Taken: HOLD (Entire Message Held for Review) Reason: Encrypted Document Detected Attachment Name: Merged statements 2021.pw.docx Policy Name: Default Attachment Management Definition - Block Dangerous File Types Detected as: docx Size: 493056 bytes Action Taken: HOLD (Entire Message Held for Review) Reason: Encrypted Document Detected Attachment Name: 2021 Renewal Cover letter for company.pw.docx Policy Name: Default Attachment Management Definition - Block Dangerous File Types Detected as: docx Size: 226816 bytes Action Taken: HOLD (Entire Message Held for Review) Reason: Encrypted Document Detected © 2003 - 2019 Mimecast Services Limited.
Hi CraigDykes1,
With your second sample data,
I used the solution proposed
and still got the desired result.
What is showing on yours after applying the proposed solution?
