Reformatting internal email address into First Name + Last Name

[SteveMForm]

Hi Folks

I have various columns where name values are held in the form of an email Title in the following format:

Lastname, Firstname (Department)

I want to reformat to: Firstname Lastname

(so stripping out the brackets and text in them, changing order to have Firstname first, and removing the comma).

What's the best way to do this?

Thanks

[SteveMForm]

Hi @Joe_Barry - I used your method and all worked fine. 

BTW- stupid question, but once I have created the merged column, and deleted the 'intermediary' ones, the merged column will produce the desired format with new data entries?

@Anonymous will try your method on next table - so thnaks for providing an alternate method.

[Anonymous]

Thanks for the reply from @Joe_Barry , please allow me to provide another insight:

Hi @SteveMForm ,

You can try below formula to create calculated column:

FormattedName = 
VAR FullName = Employees[EmailTitle]
VAR CommaPosition = FIND(",", FullName, 1, LEN(FullName))
VAR FirstName = MID(FullName, CommaPosition + 2, FIND("(", FullName, CommaPosition) - CommaPosition - 3)
VAR LastName = LEFT(FullName, CommaPosition - 1)
RETURN FirstName & " " & LastName

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry · Accepted solution]

Hi @SteveMForm 

Do the Values look like this Barry, Joe (BI) ?

In Power Query, try the following

• Highlight the column
• In the ribbon, click on Add Column
• Then Text after Delimter and choose , (there is an empty space after the , hit your spacebar) This will give a column with the First Name and Department
• Then Text before Delimter and choose , This will give a column with the Family Name
• Now Highlight the First column you extracted
• Then Text after Delimter and choose space (hit your spacebar) This will give a column with the Department
• Then Text before Delimter and choose space (hit your spacebar) This will give a column with the First name
• To get First Name Last Name  Highlight the First Name Column and then the Last name Column
• In the ribbon go to the Transform Tab > Merge Columns You can Name the new column and in the Separator section add a Space

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosKqrUUfDKT1XQcPLUVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Email." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email.", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([#"Email."], ", "), type text),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Inserted Text After Delimiter", "Text Before Delimiter", each Text.BeforeDelimiter([#"Email."], ","), type text),
    #"Inserted Text After Delimiter1" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter.1", each Text.AfterDelimiter([Text After Delimiter], " "), type text),
    #"Inserted Text Before Delimiter1" = Table.AddColumn(#"Inserted Text After Delimiter1", "Text Before Delimiter.1", each Text.BeforeDelimiter([Text After Delimiter], " "), type text),
    #"Merged Columns" = Table.CombineColumns(#"Inserted Text Before Delimiter1",{"Text Before Delimiter.1", "Text Before Delimiter"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Hope this helps

Joe