Forum Discussion
Converting true time value into duration in seconds
Hello, I have searched for a topic closely resembling the problem I'm encountering but haven't had much luck.
Background: I'm connecting to a folder of Excel workbooks and combining them. A new file is added each month. I can't change the source formatting.
The problem: Below is a screenshot of cell display, formula bar display, and custom format selection for a column of call center wait times. (Column J, "Longest Wait Time", is also a custom format, in h:mm:ss). I suspect that this is being chosen simply because it outputs in a format that closely resembles MM:SS, as it displays in the cell.
When I connect to this source in Power Query and combine the files, it auto-converts these columns into Date/Times, so that they look like this:
I figure the cleanest way to track this call wait data is to convert it to seconds. That's where I'm stuck.
What I've tried:
- Transforming the columns into Duration. This gave me nothing but errors.
- Transforming the columns into Decimal. No errors generated; however, I'm not sure how to make the outputted values useful. I can provide screenshots if needed.
- Adding custom columns using FORMAT(), DURATION.FROM(), and DURATION.FROMTEXT(). More errors. It's entirely possible that I didn't approach these correctly.
Yet to try:
- If I had some DAX on a custom column that could simply subtract a time of "midnight" from the true time values here, I might consider using that. However, I thought that's what DURATION.FROM() was for, but I didn't see that it accepted a second argument.
Any nudges or workarounds would be helpful here. Thank you kindly!
Hi messyjesse_,
The Query Editor just detects the type of the columns. You can change the display format in the Data pane.
Best Regards,
Dale
3 Replies
Hi messyjesse_,
The Query Editor just detects the type of the columns. You can change the display format in the Data pane.
Best Regards,
Dale
This is an old topic but I thought it worthwhile responding, especially given the number of views.
1. If you're able, change the Excel format to [h]:mm rather than h:mm - this will format it to a duration, with the [h] showing the total number of hours.
It is also useful when the duration goes over 24 hours - the value in Excel will still be correct whereas normally (formatted as time with h:mm or hh:mm) for instance you'd see 1:00 (or 01:00) for 25 hours.
2. There is also a (slightly clunky) way to resolve this properly from a time (rather than duration) in Excel:
// Add the hours from the time as an integer #"Inserted Hour" = Table.AddColumn(Source, "Hour", each Time.Hour([Average Wait Time]), Int64.Type), // Add the minutes as an integer #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Average Wait Time]), Int64.Type), // Combine [Hour], ':' and [Minute], as text to form a duration string #"Convert to Duration" = Table.AddColumn(#"Inserted Minute", "Duration", each Duration.FromText(Text.Combine({Text.From([Hour]),":",Text.From([Minute])})))I had that issue and I fixed it by adding a new columns like this :
duration = HOUR([Workflow_time])*3600 + MINUTE([Workflow_time])*60 + SECOND([Workflow_time])
