Forum Discussion

DataP's avatar
DataP
New Member
6 years ago
Solved

Split a calculated column

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 columnSplit column 1Split column 2Split column 3
1-2-3123
2-3-4234
4-5-6456

 

 

Thank you!

 

 

Need Help
  • az38's avatar
    az38
    6 years ago

    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)

6 Replies

  • mjc543's avatar
    mjc543
    Advocate II
    6 years ago

    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's avatar
      DataP
      New Member
      6 years ago

      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.

       

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        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)
  • az38's avatar
    az38
    Community Champion
    6 years ago

    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)