Returning a corrected Name field by using the start of an email address

[Bigelns]

I have a list of first names and a list of surnames but some of the names are corrupted by the inclusion of the character "¿"

I want to create a new column which returns the likely corrected first name and/or last name. I know this wont be perfect but in the very simplest case I have this example:

FIRST NAME = Kaan 

LAST NAME = Man¿i¿

EMAIL ADRESS = Kaan.Manjit@gmail.com

I want PowerBi to compare the Kaan from the email address with the first name and the Manjit from the second part of the email address (after the ".") and return 2 new columns for first name and second name such that it would be: 

NEW FIRST NAME = Kaan 

NEW LAST NAME = Manjit

This wont be correct for all my examples but it is a start.  If there is an even smarter way to write a script then even better.... e.g to solve a more complex one like this where theemail address is not formatted with the perfect way FIRST.SECOND@xxx.com 

e.g.

FIRST NAME = Kaan 

LAST NAME = Man¿i¿

EMAIL ADRESS = ManjitKaan@gmail.com or ManjitK@gmail.com

Thanks!!!

[freginier · Accepted solution]

Hey there!

So I understand correctly, you want to create two new columns (First Name & Last Name) by extracting values from the email address.

In power query try this: 

Extract First Name from Email: FirstName = Text.BeforeDelimiter([Email], ".") (This extracts the first part before the "." in the email.) 

Extract Last Name from Email:LastName = Text.BeforeDelimiter(Text.AfterDelimiter([Email], "."), "@") 

(This extracts the second part (after "." but before "@").)

Handle Complex Emails (FIRST.SECOND@xxx.com): If the email format is inconsistent, use:

Parts = Text.Split([Email], {"@", ".", "_"}),
FirstName = Parts{0},
LastName = Parts{1}

This works even for:
FIRST.SECOND@xxx.com
ManjitKaan@gmail.com
ManjitK@gmail.com

Hope this helps!

😁😁

[v-saisrao-msft]

Hi @Bigelns,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @Bigelns,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @Bigelns,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If any of the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Akash_Varuna · Accepted solution]

Hi @Bigelns Could you try these please 

• Extract the First Name:

  • Split the email address at the "@" character.
  • Extract the portion before "@" and split it by special characters like dots or capital letters..

• Extract the Last Name:

  • Similar to the first name, split the email portion before "@".
  • Use the second part of the split if a dot exists, or extract a substring based on capital letters.

• Replace Corrupted Characters:

  • Identify and remove/reconstruct corrupted characters like ¿ from the existing names.

let
   
    Source = Table.FromRows(
        {
              //Data
        },
        {"First Name", "Last Name", "Email Address"}
    ),
    
    // Extract the part of the email before @
    ExtractEmailPart = Table.AddColumn(Source, "EmailPrefix", each Text.BeforeDelimiter([Email Address], "@")),
    
    // Split the prefix by special characters
    SplitPrefix = Table.AddColumn(ExtractEmailPart, "SplitParts", each Text.Split([EmailPrefix], ".")),
    
    // Attempt to match the first name
    NewFirstName = Table.AddColumn(SplitPrefix, "New First Name", each 
        if List.Contains([SplitParts], [First Name]) 
        then [First Name] 
        else List.First([SplitParts])),
    
    // Attempt to match the last name by checking parts of the email
    NewLastName = Table.AddColumn(NewFirstName, "New Last Name", each 
        if List.Contains([SplitParts], Text.Remove([Last Name], {"¿"})) 
        then Text.Remove([Last Name], {"¿"}) 
        else List.Last([SplitParts])),
    
    // Remove intermediate columns
    RemoveColumns = Table.RemoveColumns(NewLastName, {"EmailPrefix", "SplitParts"})
    
in
    RemoveColumns

If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[freginier · Accepted solution]

Hey there!

So I understand correctly, you want to create two new columns (First Name & Last Name) by extracting values from the email address.

In power query try this: 

Extract First Name from Email: FirstName = Text.BeforeDelimiter([Email], ".") (This extracts the first part before the "." in the email.) 

Extract Last Name from Email:LastName = Text.BeforeDelimiter(Text.AfterDelimiter([Email], "."), "@") 

(This extracts the second part (after "." but before "@").)

Handle Complex Emails (FIRST.SECOND@xxx.com): If the email format is inconsistent, use:

Parts = Text.Split([Email], {"@", ".", "_"}),
FirstName = Parts{0},
LastName = Parts{1}

This works even for:
FIRST.SECOND@xxx.com
ManjitKaan@gmail.com
ManjitK@gmail.com

Hope this helps!

😁😁