Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Extract name from email

I am trying to create a custom column that extracts the name from the email address.  I need to remove both the domain name and the . between the first and last name and then i was going to have it correctly capitlised.  

 

But when i run the code below i get an error:

Expression.Error: We cannot convert the value "first last@company..." to type List.
Details:
Value=first last@company co
Type=[Type]

 

Text.Proper([Assigned Tech],Text.BeforeDelimiter([Assigned Tech],"@",Text.Replace([Assigned Tech],"."," ")))

 

Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous ,

    if you have a single value like [email protected] you can use the following M-code

     

    = Text.Proper(Text.BeforeDelimiter(Text.Replace("[email protected]", ".", " "),"@"))

     

    to get this:

     

     

    If you have a column in a table use the following M-code:

     

    // Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgtSS3SS0rMyXFIyc9NzMzTK83WSyxVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-Mail Address" = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"E-Mail Address", each Text.BeforeDelimiter(_, "@"), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter","."," ",Replacer.ReplaceText,{"E-Mail Address"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"E-Mail Address", Text.Proper, type text}})
in
    #"Capitalized Each Word"

     

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

     

     

     

     

     

     

1 Reply

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi Anonymous ,

    if you have a single value like [email protected] you can use the following M-code

     

    = Text.Proper(Text.BeforeDelimiter(Text.Replace("[email protected]", ".", " "),"@"))

     

    to get this:

     

     

    If you have a column in a table use the following M-code:

     

    // Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgtSS3SS0rMyXFIyc9NzMzTK83WSyxVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-Mail Address" = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"E-Mail Address", each Text.BeforeDelimiter(_, "@"), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter","."," ",Replacer.ReplaceText,{"E-Mail Address"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"E-Mail Address", Text.Proper, type text}})
in
    #"Capitalized Each Word"

     

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)