Forum Discussion

Vendelin's avatar
Vendelin
New Member
3 years ago
Solved

New column sorted alphabetically

I have column A, with many letters separated by a comma. I want a new column B, where i sort column A alphabetically.

See example Fig 1

Fig 1

Column A Column B
A,B,CA,B,C
B,C,AA,B,C
A,C,BA,B,C
B,A,CA,B,C
B,C,DB,C,D
D,A,CA,C,D
C,E,AA,C,E

 

Can anyone help me with a code for this? 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Vendelin 

    please try 

    ColumnB =
VAR String = 'Table'[ColumnA]
VAR Items =
    SUBSTITUTE ( String, ",", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
    GENERATESERIES ( 1, Length, 1 )
VAR T2 =
    SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
RETURN
    CONCATENATEX ( T2, [@Item], ",", [@Item], ASC )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Vendelin 

    please try 

    ColumnB =
VAR String = 'Table'[ColumnA]
VAR Items =
    SUBSTITUTE ( String, ",", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
    GENERATESERIES ( 1, Length, 1 )
VAR T2 =
    SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
RETURN
    CONCATENATEX ( T2, [@Item], ",", [@Item], ASC )