Forum Discussion

Anmolgan's avatar
Anmolgan
Post Prodigy
6 years ago
Solved

How to split column?

I have SAP BW on premise as my data source, I need to split a column the string goes like this: 10/10/GO11/P001

 

I need to extract out right most delimeter in this case is P001, now I could have easily done this using query editor, but problem is I cannot duplicate my column whenever I duplicate I get an error stating: 

 

The field '[ZCUST_MA___F4].[LEVEL01].[2ZCUST_MA___F4]' already exists in the record.
Details:

 

So I cannot use the query editor here, is there any way I can write a DAX to split this one?

Need Help
Show and Tell

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anmolgan ,

     

    you have to make two New Column by using below DAX formula:

     

    First_Column =
LEFT (
    SUBSTITUTE ( Table1[Name], "/", "-" ),
    SEARCH ( "-", SUBSTITUTE ( Table1[Name], "/", "-" ) ) - 1
)

     

    Last name =
RIGHT (
    SUBSTITUTE ( Table1[Name], "/", "-" ),
    LEN ( SUBSTITUTE ( Table1[Name], "/", "-" ) )
        - SEARCH ( "-", SUBSTITUTE ( Table1[Name], "/", "-" ) )
)

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

     

    Regards,

    Gaurav Raj Singh

    LinkedIN : https://www.linkedin.com/in/gauravrajsingh/

    • Anmolgan's avatar
      Anmolgan
      Post Prodigy
      6 years ago

      Anonymous Substitute function  is not helping me in this case, I dont want to replace my delimeter with - I need to extract out right most values after /

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi Anmolgan ,

        Please investigate the applied steps (panel on the right) and find the first erroneous step. You also could create a blank query to split the column and check if it works. 

         

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.