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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Sorting Text Column As Extracted Numbers?

Hey all,

 

I have a column that looks something like this: AA1, AA2, AA10, AA12, AA20

 

When I sort the column however, it treats it as a text and does the following:

AA1

AA10

AA12

AA2

AA20

 

I need to instead order it as follows:

AA1

AA2

AA10

AA12

AA20

 

I am using a DirectQuery so I cannot use index columns. I also tried extracting the numbers from the column then sorting by that number column but I got a circular dependency error. The calculated column formula was :

NumExtract = if(LEFT(table[col], 2)="AA", (MID(table[col], 3, LEN(table[col])-1)), table[col])

 

Anyone know how I might be able to sort this column properly?

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

When I try and add in that measure column I get the below error. The format of the ranked column is Decimal Number though so I don't know why it would be treating it as Char or Currency.

JP2018_0-1628169845159.png

 

edit: I found the solution using my num extract column! I placed it at the top of the row list in the matrix, changed the format to whole number, then sorted the matrix by that column. In order to hide it, I changed the name of the column to "." and minimized the column width.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @Anonymous ,

Please create a measure as below and apply it onto the related visual. Then sort the visual data by this measure just as below screenshot....

Measure =
RANKX (
    ALL ( 'table' ),
    CALCULATE ( MAX ( 'table'[NumExtract] ) ),
    ,
    ASC,
    DENSE
)

yingyinr_1-1628146213052.png

Best Regards 

Anonymous
Not applicable

When I try and add in that measure column I get the below error. The format of the ranked column is Decimal Number though so I don't know why it would be treating it as Char or Currency.

JP2018_0-1628169845159.png

 

edit: I found the solution using my num extract column! I placed it at the top of the row list in the matrix, changed the format to whole number, then sorted the matrix by that column. In order to hide it, I changed the name of the column to "." and minimized the column width.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.