Sorting column that contains both Text and Numeric values

[flyinggnugget]

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:

Values	Is Numeric
Yes	False
1	True
Matthew is 12 years old	False
2	True
Jonathan	False
11	True

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:

Values	Is Numeric
1	True
11	True
2	True
Jonathan	False
Matthew is 12 years old	False
Yes	False

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:

Values	Is Numeric
1	True
2	True
11	True

Thanks for looking into this. Appreciate it

[v-xinruzhu-msft]

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.

[Ahmedx]

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

[Ahmedx]

pls try this