Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kreg
Helper I
Helper I

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.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

1 ACCEPTED SOLUTION
Ritaf1983
Super User
Super User

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

Ritaf1983_0-1685249242443.pngRitaf1983_1-1685249296849.png

2. Trim your columns

Ritaf1983_2-1685249995592.png

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

Ritaf1983_3-1685250111227.png

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

Ritaf1983_4-1685250225614.png

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

Ritaf1983_5-1685250327880.png

Result :

Ritaf1983_6-1685250371240.png

 

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

View solution in original post

4 REPLIES 4
Kreg
Helper I
Helper I

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.

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"
   Ritaf1983_1-1685409691971.png

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
Super User
Super User

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

Ritaf1983_0-1685249242443.pngRitaf1983_1-1685249296849.png

2. Trim your columns

Ritaf1983_2-1685249995592.png

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

Ritaf1983_3-1685250111227.png

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

Ritaf1983_4-1685250225614.png

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

Ritaf1983_5-1685250327880.png

Result :

Ritaf1983_6-1685250371240.png

 

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

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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.