Forum Discussion
Sorting Column based on the another column
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”.
4 Replies
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
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.
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]))
