Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
It would be better if you do it in power query like this
pls try this
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
60 |