Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Splitting one column into three columns using DAX

I have a combined field that has area, unit, and supervisor. I can split it in power query but it gets messy as some fields only have a unit name. I'm looking for IF( SEARCH("-") THEN SPLIT, else "unit" but that ignores the second hyphen. Also I do not think SPLIT is a DAX function.

 

column value: "area - unit - supervisor"

I want to generate seperate columns for area, unit, and supervisor.

 

Is there a dax function for splitting columns based on deliminators? It's a basic function in Tableau but trying to translate it into Power BI Dax

Need Help
Tips and Tricks

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    2 years ago

    in power query you can right click on a column and split by delimiter and other options.  It is also better from a performance perspective to create calculated columns in power query. 

     

    if you need more help than that, i think you will need to provide a sample of your data (in text form) - it is quite difficult to provide a accurate solution without knowing what the data looks like.

     

    https://support.microsoft.com/en-au/office/split-a-column-of-text-power-query-5282d425-6dd0-46ca-95bf-8e0da9539662

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I found the simplest solution was to create two split by deliminiter functions in query. That allowed me to break up the fields but it would not allow me to create an IF condition. I had to create groups and do other manual clean up actions to get the data to an approximation of what is needed. It isn't exact but it is functional.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Based on your description, I made simple samples and you can check the results as follows:

     

    DAX expression:

    Area = VAR _CONTAIN = CONTAINSSTRING(MAX('Table'[Text]),"-")
VAR _PATHITEM = SUBSTITUTE(MAX('Table'[Text]),"-","|")
RETURN
IF(_CONTAIN,PATHITEM(_PATHITEM,1),"")

Unit = VAR _CONTAIN = CONTAINSSTRING(MAX('Table'[Text]),"-")
VAR _PATHITEM = SUBSTITUTE(MAX('Table'[Text]),"-","|")
RETURN
IF(_CONTAIN,PATHITEM(_PATHITEM,2),MAX('Table'[Text]))

Supervisor = VAR _CONTAIN = CONTAINSSTRING(MAX('Table'[Text]),"-")
VAR _PATHITEM = SUBSTITUTE(MAX('Table'[Text]),"-","|")
RETURN
IF(_CONTAIN,PATHITEM(_PATHITEM,3),"")

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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