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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.