Split a calculated column

[DataP]

Hello, 

I am trying to split a calculated column but I haven't been able to find how. I was planning to use the "split column" option built on PowerBi but once I went to "Transform data" my calculated column does not show up, so I can't use the "split column" option. Does anybody know how I can split the data in a calculated column? This is an example of what I would like to get: 

Calculcated column	Split column 1	Split column 2	Split column 3
1-2-3	1	2	3
2-3-4	2	3	4
4-5-6	4	5	6

Thank you!

[az38]

Hi @DataP 

try new calculated columns

Split column 1 = LEFT([Calculcated column], SEARCH("-", [Calculcated column]) - 1 )

Split column 2 = MID([Calculcated column], LEN([Split column 1]) + 2, LEN([Calculcated column]) - SEARCH("-", [Calculcated column], LEN([Split column 1]) + 2 ) )

Split column 3 = RIGHT([Calculcated column], LEN([Calculcated column]) - LEN([Split column 1]) - LEN([Split column 2]) -2)

[mjc543]

Single-click the heading of the column you'd like to split, then click the drop-down labeled 'Split Column' in the 'Transform' group on the 'Home' tab.

[DataP]

Hi mjc543, 

Thank you for your answer, I tried that but the issue I have is that my calculated column does not apppear in the query editor or "transform data". I believe the column on your screenshot is not a calculated column. 

This is why I can't find a way to split my column.

[mjc543]

Oh ok I misunderstood, my fault. Question: if it is in fact a "calculated" column - it must have been calculated from something, no? So logically the individual components already exist somewhere? Perhaps they got removed as an applied step in Power Query? If so, delete that step and they will be restored and there will be no need to split.

[az38 · Accepted solution]

@DataP 

or (and thats much better):

Split column 1 = PATHITEM(SUBSTITUTE([Calculcated column], "-", "|"), 1)
Split column 2 = PATHITEM(SUBSTITUTE([Calculcated column], "-", "|"), 2)
Split column 3 = PATHITEM(SUBSTITUTE([Calculcated column], "-", "|"), 3)

[DataP]

@az38 

This is a beautiful solution and worked great for me. Thank you!