Forum Discussion
Text to time
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
Community ChampionHi 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.
- aj1973
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?
It's linked to a huge SQL database.
I imported it, and indeed the steps work, but with an error. For those who go over 24 hours. They are indicated with the date:
One row looks like this: 2/01/1900 8:15:00
After converting it to Date/Time it stays the same
And when I move to Time again, it is 8:15:00, but it should be 56:15:00
- aj1973
"but it should be 56:15:00" why!? is this a Duration type?