Forum Discussion
Solved
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 f...
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])})))