Number sorting in text format

[Gnanasekar]

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 column, it 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

[MFelix · Accepted solution]

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

[letizia]

Hello, what if I have both negative and positive value? How can I use this method properly?

[Ahmedx]

I think you need to add a column and just write like this:

Sort_Order = VALUE([Data])

[sya]

Hi @MFelix , 

I tried this formula and an error popped up: A circular dependency was detected

[Digger]

@MFelix An argument of function 'REPT' has the wrong data type or has an invalid value.

[MFelix]

Hi @Digger,

Can you share the formula you are using?

•  

[Digger]

@MFelix Problem is 

LEN ( 'Table'[Column] )

is > than 4 

[MFelix]

Hi @Digger what type of data you have on this column? Can you share some sample data

[MFelix]

What is the text you want to combine can you give a sample? 

[Digger]

@MFelix Problem is 

LEN ( 'Table'[Column] )

is > than 4