Forum Discussion
Calculated column with comma delimited text split into columns in a new table
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
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-BfAIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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.
7 Replies
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.
Hi v-xinruzhu-msft,
Thank you very much for your support. It's worked and gave expected output as I want. 🤗💙
Best Regards,
Malsha
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
Hi,
This is a calculated column. Therefore I can't use the power query editor to this. Is there any solution to do this?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-BfAIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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?)
