Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 brian.quinn@ForeignDomain.com. (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 <First.last@Mydomain.com> 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" <brian.quinn@ForeignDomain.com> To first last <First.last@Mydomain.com> 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
Solved! Go to Solution.
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
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 keri.keenan@MyDomain.com)
Hi Can you release the following email please. Thank You Regards Keri Keenan From: Postmaster <postmaster@MyDomain.com> Sent: 31 May 2021 16:18 To: Keri Keenan <keri.keenan@MyDomain.com> 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" <keri.keenan@MyDomain.com> To FIRSTNAME LASTNAME<FIRSTNAME.LASTNAME154@btinternet.com> 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?
@mussaenda The data i would like to recieve out of the example data would be FIRSTNAME.LASTNAME154@btinternet.com
I should mention that the i have been trying to run this a report to gather all the email address's/domains that have been blocked by our email filter
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
@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
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 , You need a split column on From, then text Between < > first occurrence
like
Text.BetweenDelimiters(List.Last(List.FirstN(Text.Split([Column], "From "),2)), "<", ">")