Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sort column with numbers and text

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!

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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%20the%20Replace%20Values%20command.