Forum Discussion
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 [email protected].
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:
name | profile | email
Ragnar Kurt Weitz | PR | [email protected].
Martha Weitz | NW | [email protected]
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 sensible5. Add custom Column with code:
if [Profile] = "pr" then [Name.1]&[Profile]&"."&[Name.2]&"." & " @comapny.com"
else
[Name.1]&"."&[Name.2]&"."& " @comapny.com"Result :
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
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 ) ))RETURNSUBSTITUTE ( 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
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 sensible5. Add custom Column with code:
if [Profile] = "pr" then [Name.1]&[Profile]&"."&[Name.2]&"." & " @comapny.com"
else
[Name.1]&"."&[Name.2]&"."& " @comapny.com"Result :
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 🙂
