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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile
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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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