Converting true time value into duration in seconds

[messyjesse_]

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!

[GregPBI]

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])

[ChemEnger]

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])})))

[v-jiascu-msft · Accepted solution]

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