Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX formula help splitting string using delimiters

My sample column contains strings with multiple delimiters. For example:

Sample Column
abc/defg/klmno
abc/defg
abc

 

Only using the delimiter "/", I want to split it into 3 separate columns that are named: Column 1, column 2, and column 3. So the result should look like this:

Sample ColumnColumn 1Column 2Column 3
abc/defg/klmnoabcdefgklmno
abc/defgabcdefg 
abcabc  

 

I can't use number of characters because these change from row to row. Can anyone please help me how to write this in DAX? I can't use power query in this instance.

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    thank you for your feedback.

    I added one by one into the orginal table -> please check the attached pbix file.

     

     

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I suggest doing this in Power Query Editor.

    In case power query editor is not accessible, please try the below in order to create a new table.

    Please check the below picture and the attached pbix file.

     

     

     

    New Table = 
SUMMARIZE (
    ADDCOLUMNS (
        ADDCOLUMNS ( 'Table', "@Path", SUBSTITUTE ( 'Table'[Sample Column], "/", "|" ) ),
        "@column1", PATHITEM ( [@Path], 1 ),
        "@column2", PATHITEM ( [@Path], 2 ),
        "@column3", PATHITEM ( [@Path], 3 )
    ),
    'Table'[Sample Column],
    [@column1],
    [@column2],
    [@column3]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I think it is too complicated for me to create a new table, as I am not sure how it affects future capability to refresh or update the underlying dataset.

       

      Is there any way to individually create these columns from the original table, by adding new columns?

       

      I tried exploring option to count the number of characters in reference to the / delimiter, etc but kind of got even more confused than before...

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        thank you for your feedback.

        I added one by one into the orginal table -> please check the attached pbix file.