Calculated column with comma delimited text split into columns in a new table

[Malsha]

Hi,

I have a calculated column called 'Supervisors' in 'Table1' that is delimited by commas which I would like to split into separate columns in a new table called 'Table2'. As 'Supervisors' is a calculated column I am not able to use split column in Power Query, in any case I would like a separate table.

Any help most appreciated. Many thanks

Table1

Employee_ID                                   Supervisors
emp_key_00003124                        emp_key_00001002,emp_key_00002523
emp_key_00000716                        emp_key_00002523,emp_key_00003314
emp_key_00000450                        emp_key_00000155,emp_key_00002832,emp_key_00003879

Table2
Employee_ID                                   Supervisor1                        Supervisor2                        Supervisor3               
emp_key_00003124                        emp_key_00001002            emp_key_00002523
emp_key_00000716                        emp_key_00002523            emp_key_00003314
emp_key_00000450                        emp_key_00000155            emp_key_00002832           emp_key_00003879     

[Ritaf1983 · Accepted solution]

Hi @Malsha 
If I understood you correctly and you need to split the column with Dax, please refer to the linked video:
https://www.youtube.com/watch?v=j0A6CYg-BfA

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

[Anonymous · Accepted solution]

Hi @Malsha 

You can refer to the following calculated table

Table 2 =
VAR a =
    ADDCOLUMNS ( 'Table', "path", SUBSTITUTE ( [Supervisors], ",", "|" ) )
RETURN
    SUMMARIZE (
        ADDCOLUMNS (
            a,
            "Supervisor1", PATHITEM ( [path], 1 ),
            "Supervisor2", PATHITEM ( [path], 2 ),
            "Supervisor3", PATHITEM ( [path], 3 )
        ),
        [Employee_ID],
        [Supervisor1],
        [Supervisor2],
        [Supervisor3]
    )

Output

Best Regards!

Yolo Zhu

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

[Malsha]

Hi v-xinruzhu-msft,
Thank you very much for your support. It's worked and gave expected output as I want. 🤗💙

Best Regards,
Malsha

[Ashish_Mathur]

Hi,

This cannot be done with a single DAX calculated column formula.  One will have to write one formula for each column.  The problem would be scalability (what if there are 6 commas in a cell?)

[amitchandak]

@Malsha , In power query use split by delimiters, Advance options columns

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Power Query Split Column , Split Column By Delimiter: https://youtu.be/FyO9Vmhcfag

[Malsha]

Hi,
This is a calculated column. Therefore I can't use the power query editor to this. Is there any solution to do this?

[Ritaf1983 · Accepted solution]

Hi @Malsha 
If I understood you correctly and you need to split the column with Dax, please refer to the linked video:
https://www.youtube.com/watch?v=j0A6CYg-BfA

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

[Malsha]

Hi Ritaf1983,
Thank you for your support.🤗