Forum Discussion
Text to time
Looking for the best way to convert a text column to a time column, allowing me to calculate the sum, average, ...
Data comes in looking like time, but is formatted as text. For most only the time is visible, for those going over 24 hours, you'll see the days as well.
Hey hansbogaert
this was something new for me and I thank you for making me discover it.
Here are the steps:
let's say your column looks like this in your DB
You need to add a custom column and convert that column to Sum of seconds
here is the formula Duration.TotalSeconds([Duration] - #datetime(1899,12,31,0,0,0))
Change the Column Type to number
Then add a Dax formula like this:
And here is the DAX
Total Duration =var vSeconds=SUM(Sheet1[Dur])var vMinutes=int( vSeconds/60)var vRemainingSeconds=MOD(vSeconds, 60)var vHours=INT(vMinutes/60)var vRemainingMinutes=MOD(vMinutes,60)var vDays=INT(vHours/24)var vRemainingHours=MOD(vHours,24)returnvDays&" D : "&vRemainingHours&" H : "&vRemainingMinutes&" M : "&ROUND(vRemainingSeconds,0)& " S "Hope it works for you.
Attached the Sample Pbix
https://drive.google.com/file/d/1wU_VFfS69XSIPeYkT2v12-r1s84QQb3G/view?usp=sharing
10 Replies
Hi hansbogaert
change the type of the column to Date/Time, then change it into Time...In Power query
Hi aj1973 ,
Thanks for your fast reaction.
When I try to do this, I get the message that I need to convert my data to Import Mode instead of DirectQuery.
Oh yes, you can't do it in DQ mode connection. you need to do the cleaning directly to the source. Is the source an excel file?
