Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
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.
Proud to be a Super User!
Hello
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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |