Forum Discussion

flyinggnugget's avatar
flyinggnugget
Frequent Visitor
2 years ago

Sorting column that contains both Text and Numeric values

I have two columns, one contains a mixture of text and numeric values, the other column contain boolean values on whether the first column is numeric or not. Values column is currently set as text format.

Example:

ValuesIs Numeric

Yes

False
1True
Matthew is 12 years oldFalse
2True
JonathanFalse
11True

 

As the values column contains text, when I try to sort it, it will sort as text format as seen in the following example, which is fine.

Example of sorted table:

ValuesIs Numeric
1True
11True
2True
JonathanFalse
Matthew is 12 years oldFalse
YesFalse

 

Is there a way I can get the values column to sort as number format when I filter for only numeric values?

Example of sorted when applying filter for Is Numeric = True:

ValuesIs Numeric
1True
2True
11True

 

Thanks for looking into this. Appreciate it

Need Help

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    It would be better if you do it in power query like this

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi flyinggnugget 

    You can create a calculated column

     

    Rank =
IF (
    [Is Numeric] = TRUE (),
    RANKX (
        FILTER ( 'Table', [Is Numeric] = TRUE () ),
        VALUE ( [Values] ),
        ,
        ASC,
        DENSE
    ),
    RANKX ( FILTER ( 'Table', [Is Numeric] = FALSE () ), [Values],, ASC )
)

     

     

    Output

    Best Regards!

    Yolo Zhu

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