Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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 :
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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:
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 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 :
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 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
106 | |
97 | |
38 | |
32 |
User | Count |
---|---|
153 | |
122 | |
77 | |
74 | |
44 |