Forum Discussion
Sort ordering string with number and text
Hi All
I have a column that I need to show in the correct order the element inside are:
1.1. XXXXXX, 1.2. xxxxxxx, 1.10. xxxxx .... 2.20 xxxxxx ect
at the moment they are sorting like this 1.1, 1.10.1.2, 2.20 how I order them in a easy way?
Hi Raffa Could you try this please
Create a calculated column to extract the numeric parts of the string and use them for sorting.SortKey = CONCATENATE( LEFT([Column], FIND(" ", [Column]) - 1), REPLACE([Column], ".", "", FIND(" ", [Column])) )Select the original column.
In the "Column Tools" tab, use the "Sort by Column" feature to sort it by the helper column created above.
If this post helped please do give a kudos and accept this as a solution
Thanks In AdvanceHi Raffa
In the query editor, select that column and go to the Add Column tab, click Extract then Text Before Delimiters. Use ". " (a period and a space) as the delimiter
Convert the custom columns data type in to decimal then apply the changes to the query
In the report designer, select the original column to enable the Column toosl contextual tab. Select sort by and the custom column created in the query editor.
While you may use DAX to create a calculated column to extract the numbers, you won't be to successfuly sort the original column by the calcualted column as that will cause a circular dependency error. Number2 column in the screenshot below references Text
6 Replies
Hi Raffa Could you try this please
Create a calculated column to extract the numeric parts of the string and use them for sorting.SortKey = CONCATENATE( LEFT([Column], FIND(" ", [Column]) - 1), REPLACE([Column], ".", "", FIND(" ", [Column])) )Select the original column.
In the "Column Tools" tab, use the "Sort by Column" feature to sort it by the helper column created above.
If this post helped please do give a kudos and accept this as a solution
Thanks In AdvanceHi Raffa
In the query editor, select that column and go to the Add Column tab, click Extract then Text Before Delimiters. Use ". " (a period and a space) as the delimiter
Convert the custom columns data type in to decimal then apply the changes to the query
In the report designer, select the original column to enable the Column toosl contextual tab. Select sort by and the custom column created in the query editor.
While you may use DAX to create a calculated column to extract the numbers, you won't be to successfuly sort the original column by the calcualted column as that will cause a circular dependency error. Number2 column in the screenshot below references Text
Hey there!
You could try a DAX formula:
SortColumn =
VAR Parts = SUBSTITUTE([ColumnName], ".", ",")
RETURN VALUE(Parts)This converts "1.10" into 1.10, allowing proper numerical sorting.
Use this new column to sort your main column.Also, if your data is stored in a SQL database, try:
SELECT [ColumnName]
FROM Table
ORDER BY CAST(ColumnName AS FLOAT)This forces numeric sorting instead of text sorting.
Hope this helps!
😁😁
Hi Raffa ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Regards,
Rama U.
Hi Raffa ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.
Regards,
Rama U.
