cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gnanasekar Member
Member

Number sorting in text format

Hi All,

 

A column in power bi report contained two datatypes data (Number & Text)

Test:

1 , 22, 2,178, 3, Red, 10, 20, 30, Yellow, 100, 110,  200, 250,  Green

 

If I convert number (Decimal or Whole)  data type 'Red, Yellow, Green' will be gone (or) get error. So I changed text datatype

in text datatype If I sort Test columnit was coming order like

1

10

100

110

178

2

20

22

200

250

3

30

Green

Red

Yellow

(or reversed of this order)

it was ordering based on letter.

But I need order based on value ( Like 1,2,3,10,20,22,30,100,...Green, Red, Yellow)

 

By

Gnanasekar

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Number sorting in text format

Hi @Gnanasekar,

 

Taking into account that you need the values by order you can always add a column with the following formula:

Sort_Order =
IF (
    NOT ( ISERROR ( 'Table'[Column] + 0 ) )
        = TRUE ();
    CONCATENATE ( REPT ( 0; 4 - LEN ( 'Table'[Column] ) ); 'Table'[Column] );
    'Table'[Column]
)

Then use this column to sort out the visual.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




1 REPLY 1
Highlighted
Super User
Super User

Re: Number sorting in text format

Hi @Gnanasekar,

 

Taking into account that you need the values by order you can always add a column with the following formula:

Sort_Order =
IF (
    NOT ( ISERROR ( 'Table'[Column] + 0 ) )
        = TRUE ();
    CONCATENATE ( REPT ( 0; 4 - LEN ( 'Table'[Column] ) ); 'Table'[Column] );
    'Table'[Column]
)

Then use this column to sort out the visual.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!