Two improvements around email

[Kreg]

Hi,

I have a table named "workers" where there is a column "profile", "name", and "email".

I need a query which gives me for exemplary name Ragnar Kurt Weitz such address email ragnarpr.kurt.weitz@company.com.

The problem is that some names in the table have three not two words, like above. After each of them there is a need to add . Also for those where in profile column you see PR it should add pr at the end, finally I should see address like that:

ragnarpr.kurt.weitz@company.com.

name                        | profile    | email

Ragnar Kurt Weitz    | PR          | ragnarpr.kurt.weitz@company.com.

Martha Weitz            | NW        | martha.weitz@comapny.com

[Kreg]

Thanks Ritaf, just last question. Can we do the fifth point with Dax query? I mean not from that level which you showed, but from the Report view.

[Ritaf1983]

Hi @Kreg 
It can also be done with DAX.
Add the calculated column using the following Code:

Formated mail =

VAR Name_with_spaces =

    IF (

        [Profile] = "pr",

        LEFT ( [Name], SEARCH ( " ", [Name] ) - 1 )

            & LOWER ( 'Table (2)'[Profile] ),

        LEFT ( [Name], SEARCH ( " ", [Name] ) - 1 )

    )

        & RIGHT (

            'Table (2)'[Name],

            LEN ( 'Table (2)'[Name] )

                - LEN ( LEFT ( [Name], SEARCH ( " ", [Name] ) - 1 ) )

        )

RETURN

    SUBSTITUTE ( Name_with_spaces, " ", "." ) & "@company.com"

   

Link to the updated sample file 

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

[Ritaf1983 · Accepted solution]

Hi @Kreg 
You can achieve your goals by following these easy steps at PQ:
1. Split your name column by first space:

2. Trim your columns

3. On your second column replace space by . ( space is just pressing space on keyboard)

4. Lowercase your profile column
* M language is case sensible

5. Add custom Column  with code:
if [Profile] = "pr" then [Name.1]&[Profile]&"."&[Name.2]&"." & " @comapny.com"
else
[Name.1]&"."&[Name.2]&"."& " @comapny.com"

Result :

Link to a sample file 

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

[Kreg]

Rita, thanks again.

I saw one issue. With some non-English names it does not work. I mean for example two dotted above `a` and many more similar cases. When I manually set it to English `a` it works, but it should set upfront all such cases. 

In transform table the best. I choose that method in fact, seems to be easier 🙂