Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
messyjesse_
Frequent Visitor

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.

 

Capture.PNG

 

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:

 

Capture2.PNG

 

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!

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @messyjesse_,

 

The Query Editor just detects the type of the columns. You can change the display format in the Data pane.

Converting_true_time_value_into_duration_in_seconds

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
GregPBI
Frequent Visitor

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
Advocate IV
Advocate IV

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
Employee
Employee

Hi @messyjesse_,

 

The Query Editor just detects the type of the columns. You can change the display format in the Data pane.

Converting_true_time_value_into_duration_in_seconds

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.