Forum Discussion

Mtaberna's avatar
Mtaberna
Frequent Visitor
4 years ago

split column by "|" NOT in Power Bi Query

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 b...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    Mtaberna
    Frequent Visitor
    4 years ago

    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