- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])})))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-12-2024 01:30 PM | |||
03-16-2024 07:59 PM | |||
02-26-2024 01:49 PM | |||
11-14-2023 01:39 PM | |||
Anonymous
| 10-27-2022 02:24 AM |
User | Count |
---|---|
118 | |
73 | |
56 | |
56 | |
43 |
User | Count |
---|---|
181 | |
121 | |
82 | |
69 | |
54 |