Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
newpi
Helper V
Helper V

How do I sort values in a column to be in the same order.

Hi,
I have a column with comma seperated values like

1.

a,b,c,d

2.

b,c,d,a

3.a,d,b,c
4.a,b,c
5.b,c,a

 

I don't care about the order. Rows 1,2 & 3 are the same and row 4&5 are the same. Just the alphabets are jumbled. I want to retain the rows but I want the values to be in the same order and not jumbled.. How do I organize this column so its not jumbled? 

1 ACCEPTED SOLUTION
newpi
Helper V
Helper V

6 REPLIES 6
newpi
Helper V
Helper V

I could solve using this video
https://www.youtube.com/watch?v=GInkqF-wuDM

tamerj1
Super User
Super User

Hi @newpi 
You can create a new calculated column using DAX. Here is a sample file with the solution https://www.dropbox.com/t/1KQTM2trlplD0Gnw

Values Correct Order = 
VAR String = Data[Values]
VAR ConvertPath = SUBSTITUTE ( String, ",", "|" )
VAR Length = PATHLENGTH ( ConvertPath )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Items", PATHITEM ( ConvertPath, [Value] ) )
RETURN
    CONCATENATEX ( T2, [@Items], ",", [@Items], ASC )

 

@tamerj1 I'm getting an error "The arguments in GenerateSeries function cannot be blank." 

What am I missing? Also I'm creating a new column in the table and the using this formula and my values in the column are not of equal length, meaning its more like ( a, abc, ab) etc.

use_cases correct order =
VAR String = new_business_use_cases[use_cases]
VAR ConvertPath = SUBSTITUTE ( String, ", ", "|" )
VAR Length = PATHLENGTH ( ConvertPath )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Items", PATHITEM ( ConvertPath, [use_cases] ) )
RETURN
CONCATENATEX ( T2, [@Items], ", ", [@Items], ASC )

@newpi 
You have blank values. Please use https://www.dropbox.com/t/47luIKUpiLZmAFIO

Values Correct Order = 
VAR String = Data[Values]
VAR ConvertPath = SUBSTITUTE ( String, ",", "|" )
VAR Length = COALESCE ( PATHLENGTH ( ConvertPath ), 1 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Items", PATHITEM ( ConvertPath, [Value] ) )
RETURN
    CONCATENATEX ( T2, [@Items], ",", [@Items], ASC )

 

Thanks @tamerj1 but now getting a new error.
Cannot convert value 'Remote Work' of type Text to type Integer.
"Remote Work" is one of the values (ab c)

Good morning @newpi 
I have tried all types of texts I could think of but failed to simulate the error you receive. Please share some sample data including the line that generates the error so I can simulate it and fix it. Thank you.

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.