Forum Discussion
M Language - convert duration to seconds
Apologies, but I really don't know M at all and I've run into difficulties with time and duration. I have a table with, amongst other things, 15 columns of durations in the format xx:xx:xx . I currently have these 15 columns as a strings. I'd like to know the custom function to convert this to seconds - i.e., (number before first : delimiter * 3600) + (number between first and second : delimiter *60 ) + number after last delimiter.
I can do this in Excel with LEFT, MID, LEN and FIND, and I guess I could do the same as calculated columns in DAX, but I don't know how to do it in Power Query with M. Is it quicker to do it that way?
I haven't upoad a pbi file here as I'm hoping it's quite a self-explanatory question.
Many thanks
If you look on the right hand side of this web page, you'll see a 'Recommendations' panel and they might help you.
Alternatively, if you get the column into a duration data type, you can use function:
https://learn.microsoft.com/en-us/powerquery-m/duration-totalseconds
3 Replies
You can either add a column or transform the existing column.
- Ensure your dataColumn is type as a duration
- To add a column, navigate to the Add Custom Column item and, for a formula:
=Duration.TotalSeconds([dataColumn])
If you look on the right hand side of this web page, you'll see a 'Recommendations' panel and they might help you.
Alternatively, if you get the column into a duration data type, you can use function:
https://learn.microsoft.com/en-us/powerquery-m/duration-totalseconds
Thanks. They're all about how to do it in DAX though, and I think I can do that. I'm trying to do it in M/Power Query, rather than going through the process of 'copy column, split by delimiter, do arithmetic, delete columns again' 15 times. Sorry, guess I'm being lazy!
