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,

 

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