Forum Discussion

Kreg's avatar
Kreg
Helper I
3 years ago
Solved

Two improvements around email

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.wei...
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    3 years ago

    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's avatar
Kreg
Helper I
3 years ago

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's avatar
    Ritaf1983
    Super User
    3 years ago

    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