Forum Discussion

CraigDykes1's avatar
CraigDykes1
Regular Visitor
5 years ago
Solved

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 i...
Need Help
  • mussaenda's avatar
    mussaenda
    5 years ago

    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

     

mussaenda's avatar
mussaenda
Community Champion
5 years ago

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.

CraigDykes1's avatar
CraigDykes1
Regular Visitor
5 years ago

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.

  • mussaenda's avatar
    mussaenda
    Community Champion
    5 years ago

    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?

    • CraigDykes1's avatar
      CraigDykes1
      Regular Visitor
      5 years ago

      mussaenda The data i would like to recieve out of the example data would be [email protected]

       

      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

      • mussaenda's avatar
        mussaenda
        Community Champion
        5 years ago

        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