Forum Discussion

hermanos's avatar
hermanos
Frequent Visitor
3 years ago

Split Text After Second Value with Dax

Hi guys,

 

I have a sample dataset which contains mail adresses of our users. For example;
car@[email protected]

ships@[email protected]
[email protected]
[email protected]

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!

6 Replies

  • SamInogic's avatar
    SamInogic
    Icon for Super User rankSuper User
    3 years ago

    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 [email protected]

    Service:  http://www.inogic.com/services/ 

    Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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]
)

     

    • hermanos's avatar
      hermanos
      Frequent Visitor
      3 years ago

      It throws me an error. 
      "The search Text provided to function 'SEARCH' could not be found in the given text."

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Do all your values contain "@"?

     

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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]
)