DAX formula help splitting string using delimiters

[nomi]

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 Column	Column 1	Column 2	Column 3
abc/defg/klmno	abc	defg	klmno
abc/defg	abc	defg
abc	abc

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.

[Jihwan_Kim]

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

[nomi]

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 · Accepted solution]

Hi,

thank you for your feedback.

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