Sort column with numbers and text

[RickSchultz]

Hello!  I'm seen answers to this kind of question but the closest solutions don't seem to work, so I'm asking for help, please.

I have a data set with about 700,000 rows (and growing).  One column has responses to questions.  Not every row has a question, and not every question has a response, so there are lots of nulls.  As well, since new responses are being created fairly regularly, I can't just list every answer and sort them to use as a sort column.

I tried this promising answer:  https://community.powerbi.com/t5/Desktop/Number-sorting-in-text-format/m-p/237256; I also got an issue with the REPT part, but since my numbers are no more than 2 digits, I was able to just add a 0 to the first digit to try that as a sort order.  Unfortunately, when I tried to use that, I got a circular logic error.

Is there some other way to force Power BI to sort a number as a number, even if it's formatted as text?  Or another trick or workaround you've found helpful?

Thank you!

[RickSchultz · Accepted solution]

Thanks, Polly.  I should have been more clear - I apologize.  I have some numbers (1, 2, 3 and so on) and some words/phrases (1-2 years, Usually, Strongly agree and so on) in a column:

1

Strongly Agree

3

10

Disagree

Of course, the plain numbers are sorting like text, 1, 10, 11, 12, 2, 3 ,4 and so on.  

As I dug in, I found there was a lot more that I needed to do to organize this, so I found this option for a bulk replace value - haven't tried it yet, but it looks promising:  https://www.howtoexcel.org/bulk-replace-values/#:~:text=When%20inside%20the%20power%20query,press%20....

[v-rongtiep-msft]

Hi @RickSchultz ,

I have created a simple sample, please refer to it to see if it helps you.

Create a column first.

NumExtract =
IF (
    LEFT ( 'Table'[Column1], 2 ) = "AA",
    ( MID ( 'Table'[Column1], 3, LEN ( 'Table'[Column1] ) - 1 ) ),
    'Table'[Column1]
)

Then create a measure.

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

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.