"Clean" e-mail addressee and use the person's name

[i_stanescu_ro]

Hi everyone!

Hope you can help me put!

I have in BI this list of emails (fake ones). Some of them are repeating. I need to do someting in BI to remove "@test.com" and as an example -  catalina.maruntelu@test.com to be Catalina Maruntelu.

And one more thing, the emails in red have the "-xx" thing at the end of the name, I'm gona need to remove that too..

And another thing, example -  Iasmina-Cornelia.Farcas@test.com should be Iasmina-Cornelia Farcas.

Thanks so much!

fake emails

elena.popescu-ab@test.com

elena.popescu-ab@test.com

mihaela.groza-sv@test.com

mihaela.groza-sv@test.com

elena.popescu-ab@test.com

Maria.Moldovan-MS@test.com

elena.popescu-ab@test.com

maria.moldovan-ms@test.com

maria.moldovan-ms@test.com

Maria.Moldovan-MS@test.com

elena.popescu-ab@test.com

iosif-george.verinica@test.com

catalina.maruntelu@test.com

Ioana.Avram@test.com

carmen.stoian@test.com

cristina.ghinga@test.com

elena-alina.nica@test.com

Leonida.Cheregi@test.com

sinziana.calita@test.com

ion.schintee@test.com

carmen.codreanu@test.com

Marius-Adrian.Lengyel@test.com

Iasmina-Cornelia.Farcas@test.com

aurora.kosztandi@test.com

[ryan_mayu]

@i_stanescu_ro 

maybe you can try this is PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFBagMxDEXvknXtMzQECoXMqsuQxY9H9YjaUpA9A53T12kpdCahJXT9/L+epcNhQ4kE/qxnKmF0OD1WKtUHzZvjw1808wBK8NF0hivTPfT35g7G8J2mXieI617u0voM5+9wLnfhf41mLfzqIqlF8hMZCwcsnwRUJG4dzWOUSmlc8mdFg9vJkNdByyS+VGXIChmXeumMA0vEDWP3NfNaZ08q3MPvBjKKvISFZeaLTmjxivVfm0xoAyvRTdOgvRFkvN7vWNy2b2sWvyeJ75RWK0DJzdbt1IRSu8YTLGB1J4ymBv+mZa6Q/qf58QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"fake emails" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"fake emails", type text}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each let splitfakeemails = Splitter.SplitTextByDelimiter(".", QuoteStyle.None)([fake emails]), splitsplitfakeemails1 = Splitter.SplitTextByDelimiter("-", QuoteStyle.None)(splitfakeemails{1}?) in Text.Combine({Text.Start(Text.Proper([fake emails]), 5), Text.Middle(splitfakeemails{0}?, 5), " ", Text.Proper(splitsplitfakeemails1{0}?)}), type text),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Added Custom Column", {{"Custom", each Text.BeforeDelimiter(_, "@"), type text}})
in
#"Extracted Text Before Delimiter"

[i_stanescu_ro]

Hi again

I found out where to put the code, all good now, thanks!

I am left with:

line 12 - george should be George

line 17 - the same as above

line 20 - we have an extra "S" in the name

[i_stanescu_ro]

Thanks so much!

Not sure where to insert the code you gave me... Can you please help? Thanbks so much for your time!

And:

line 12 - george should be George

line 17 - the same as above

line 20 - we have an extra "S" in the neam

[ryan_mayu · Accepted solution]

pls try this

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFBagMxDEXvknXtMzQECoXMqsuQxY9H9YjaUpA9A53T12kpdCahJXT9/L+epcNhQ4kE/qxnKmF0OD1WKtUHzZvjw1808wBK8NF0hivTPfT35g7G8J2mXieI617u0voM5+9wLnfhf41mLfzqIqlF8hMZCwcsnwRUJG4dzWOUSmlc8mdFg9vJkNdByyS+VGXIChmXeumMA0vEDWP3NfNaZ08q3MPvBjKKvISFZeaLTmjxivVfm0xoAyvRTdOgvRFkvN7vWNy2b2sWvyeJ75RWK0DJzdbt1IRSu8YTLGB1J4ymBv+mZa6Q/qf58QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"fake emails" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"fake emails", type text}}),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Text Before Delimiter", each Text.BeforeDelimiter([fake emails], "@"), type text),
#"Added Custom" = Table.AddColumn(#"Inserted Text Before Delimiter", "Custom", each if Text.Start(Text.End([Text Before Delimiter],3),1)="-"

then Text.Start([Text Before Delimiter],Text.Length([Text Before Delimiter])-3) else [Text Before Delimiter]),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","-","-/",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".","/",Replacer.ReplaceText,{"Custom"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"Custom.1", Text.Proper, type text}, {"Custom.2", Text.Proper, type text}}),
#"Capitalized Each Word1" = Table.TransformColumns(#"Capitalized Each Word",{{"Custom.2", Text.Proper, type text}}),
#"Merged Columns" = Table.CombineColumns(#"Capitalized Each Word1",{"Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Text Before Delimiter"})
in
#"Removed Columns"

pls see the attachment below

[i_stanescu_ro]

Perfect!

Thanks so much for your time and help!!!

[ryan_mayu]

you are welcome