Sorting Column based on the another column

[Anonymous]

Hi All,
We have data like below image :-

Based on the sort range column I want to create one another column where sort range contains “>-“ it will be 1 and if sort range contains “>” it will be max value of the sort range rest remain same in new column.

It will helpful if I do the changes in same column “sortrange”
This column I have created to sort the “Range”.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check the formula.

Column =
IF (
    CONTAINSSTRING ( 'Table'[range], ">-" ),
    "1",
    IF (
        CONTAINSSTRING ( 'Table'[range], ">" ),
        SUBSTITUTE ( 'Table'[range], ">", "" ),
        'Table'[short range]
    )
)

Best Regards,

Jay

[Singaravelu_R]

First duplicate the shortRange column in the query editor then replaces >- values into = as given below

then split the column by non digit to digit and close and apply

then create calucate column like below

Column = if('Table'[shortRange - Copy.1]="=","1",if('Table'[shortRange - Copy.1]=">",max('Table'[shortRange - Copy.2]),'Table'[shortRange]))

[amitchandak]

@Anonymous , Looking at data remove > signs , trim space and covert to number, best can done in power query using replace values and trim under data transform

Power Query Trim and Clean: https://youtu.be/NRYPsCnS0w4

Power Query Replace Value: https://youtu.be/hkZhZbR7Kmk

[Anonymous]

@amitchandak , There is tow 17000 and >17000 presented in data
based on the sort of range, I am trying to sort "Range" column, If I am remove > sign from the column and trying to sort "Range" column based on "Sort Range" column there will be conflict between 2 rows
It will showing me x axis like this on data:

>-20000 should be palace on 1st position.