Shorting an Alphanumeric column

[anirban121]

I have a column in my data base and data type of the column is varchar.

Column data are like:

1 year

3 days

11 hours

2 months

I want to short the data like below:

11 hours

3 days

2 MONTHS

1 YEAR

BUT I am not able to do so?

• Can anyone please help me out?

   

[Anonymous]

HI @anirban121 This might be achievable but you will need to do some data cleanup and transformation inside power query editor and then create a custom function within power query.

Can you share some sample data?

Thanks

[FarhanAhmed]

What you can do is to create a custom column in power query that will extract your number and convert it to minutes

if 
Text.Contains([Column1],"hours") then Number.FromText( Text.Replace([Column1],"hours","")) * 60
else if
Text.Contains([Column1],"days") then Number.FromText( Text.Replace([Column1],"days","")) * 24 * 60
else if
Text.Contains([Column1],"Months") then Number.FromText( Text.Replace([Column1],"Months","")) * 30 * 24 * 60
else if
Text.Contains([Column1],"year") then Number.FromText( Text.Replace([Column1],"year","")) *12*30*24* 60
else 
0

Then convert this value column to "WholeNumber"

After that sort your Alphanumeric column with this value number that will sort your data as desired. 

[anirban121]

Hello

• I have created a column. And Sorted as per your suggestion, But I don't know why it's not getting sorted.

[pranit828]

Hi @anirban121 

You have to use the new created column in the chart on the intial screenshot shared and sort on that column.

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

Regards,
Pranit

[pranit828]

Hi @anirban121 

There is no easy way in which this data can be sorted as there is no datatype which can identify it. I am not saying it is impossible.

One possible and easy solution can be to create a calculated column on the WorkItem_id table created_date column and display it as one unit

Age in Months = DATEDIFF(created_date,today(), MONTH)

Alternatively you can also display it in week, day or hour
Age in Months = DATEDIFF(created_date,today(), WEEK)
Age in Months = DATEDIFF(created_date,today(), DAY)
Age in Months = DATEDIFF(created_date,today(), HOUR)

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

Regards,
Pranit

[v-yingjl]

Hi @anirban121 ,

Not certain what is your expected output based on your picrture and description. Could you please consider sharing some sample data and expected output for further discussion?

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.