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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CraigDykes1
Regular Visitor

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

1 ACCEPTED 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"

 

Untitled3.png

 

 

 

Hope this helps

 

View solution in original post

8 REPLIES 8
mussaenda
Super User
Super User

Hi @CraigDykes1 ,

 

would this work on yours?

I used your sample text and used this:

Text.BetweenDelimiters(
    Text.BetweenDelimiters(
        [Column1], """", ">"
    ), "<", ">"
)

 

 

Untitled.png

 

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.

 

Untitled2.png

 

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"

 

Untitled3.png

 

 

 

Hope this helps

 

Greg_Deckler
Super User
Super User

@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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
CraigDykes1
Regular Visitor

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 ?

amitchandak
Super User
Super User

@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)), "<", ">")

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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