split column by "|" NOT in Power Bi Query

[Mtaberna]

Hi, I neet to split this column (1-PATH col)  in  n columns by a delimitator  ( | )

I know this can be easily made with power query but the column is a measure

First row result should be:

Column 1 INFORMESLV

Column2  CM_PATENTE

Column3  DEPRE

Column4  4227004100

Maximum columns = 9

Ideas ??

Thanks

[Anonymous]

Hi  @Mtaberna ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
VAR Temp =
    ADDCOLUMNS ( 'Table', "@Parent", SUBSTITUTE ( 'Table'[1-PATH col], "|", "|" ) )
VAR NewColumns =
    ADDCOLUMNS (
        Temp,
        "@FirstLevel", TRIM ( PATHITEM ( [@Parent], 1, TEXT ) ),
        "@SecondLevel", TRIM ( PATHITEM ( [@Parent], 2, TEXT ) ),
        "@ThirdLevel", TRIM ( PATHITEM ( [@Parent], 3, TEXT ) ),
        "@FourthLevel", TRIM ( PATHITEM ( [@Parent], 4, TEXT ) )
    )
VAR Result =
    SELECTCOLUMNS (
        NewColumns,
        "Original", [1-PATH col],
        "First Level", [@FirstLevel],
        "Second Level", [@SecondLevel],
        "Third Level", [@ThirdLevel],
        "Fourth Level", [@FourthLevel]
    )
RETURN
    Result

2. Result:

Best Regards,

Liu Yang

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

[Mtaberna]

Hi Liu

That worked fine.. in a new table......but is there any other way to create the COLUMNS in the same Original Table  ??

The thing is......I need everything in one table

Thanks

[truptis]

Hi @Mtaberna ,

Try this(it has got the DAX codes) :

http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html 

[truptis]

Hi @Mtaberna ,

Refer these solutions:
https://community.powerbi.com/t5/Desktop/DAX-How-to-Split-left-a-text-column-on-Character-space/m-p/.... 

https://community.powerbi.com/t5/Power-Query/How-to-split-column-till-delimiter-using-DAX/td-p/86356... 

[Mtaberna]

Hi, thanks for your response.

The thing is that the proposed solutions work for just 2 columns.

I have a max of 9 columns, delimited by this character =  |

I need DAX code to split everything inside the column and also consider the case where 1 , 2 or more columns could be empty

New Ideas ?

Thnks

[Mtaberna]

Hi Liu

That worked fine.. in a new table......but is there any other way to create the COLUMNS in the same Original Table  ??

The thing is......I need everything in one table

Thanks