Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a sample dataset which contains mail adresses of our users. For example;
car@first@.com
ships@second@.com
car@fa.com
ships@commun.com
If I have "car" in my e-mail address, I want to delete after the first @, if "ships", after the second @.
I tried some functions such as search, mid, len but I couldn't achieve my goal.
I believe there is a lot of wisdom people here. Can you help me about how can I do this with dax?
Thank you!
@hermanos
It would be better to do this in Power Query. Try this other version
NewColumn =
SWITCH (
TRUE (),
SEARCH ( "ships", TableName[Address],, 0 ) > 0,
LEFT (
TableName[Address],
VAR aux_ =
FIND ( "@", TableName[Address], SEARCH ( "@", TableName[Address],, 0 ) + 1, 0 )
RETURN
IF ( aux_ = 0, LEN ( TableName[Address] ), aux_ )
),
SEARCH ( "cars", TableName[Address],, 0 ) > 0,
LEFT (
TableName[Address],
VAR aux2_ =
SEARCH ( "@", TableName[Address],, 0 )
RETURN
IF ( aux2_ = 0, LEN ( TableName[Address] ), aux2_ )
),
TableName[Address]
)
Do all your values contain "@"?
No, some of them has only letters.
Hi @hermanos
Try this
NewColumn =
SWITCH (
TRUE (),
SEARCH ( "ships", TableName[Address] ) >= 1,
LEFT ( TableName[Address],
FIND ( "@", TableName[Address], SEARCH ( "@", TableName[Address] ) + 1 ) - 1
),
SEARCH ( "cars", TableName[Address] ) >= 1,
LEFT ( TableName[Address], SEARCH ( "@", TableName[Address] ) - 1 ),
TableName[Address]
)
It throws me an error.
"The search Text provided to function 'SEARCH' could not be found in the given text."
Hi @hermanos,
We are bit unclear with exact requirement but as per our understanding, in the Sample data that you have provided there are two "@" in some Emails and you want to remove the 2nd "@" for cars and Ships or else the First from Cars and Second from Ships.
Can you please help us by Elaborating your Requirement in some Sentences so that we can get it and can Provide Result?
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Of course, I will try to explain further more.
Here my list;
cars@berkeley@edu |
ships@elegantthemes@com |
cars@xing.com |
ships@mozilla@com |
ships@ycombinator.com |
cars@cmu@edu |
ships@state@gov |
ships@paginegialle.it |
ships@wix.com |
cars@baidu.com |
Here desired output;
cars@ |
ships@elegantthemes |
cars@ |
ships@mozilla |
ships@ycombinator.com |
cars@ |
ships@state |
ships@paginegialle.it |
ships@wix.com |
cars@baidu.com |
I mean, If my adress contains cars I dont want the see after first @ symbol. But if I have ships in my adress, then I want to take second @ symbol as a delimiter.
I hope this was more meaningful. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |