Cannot import Duration from Excel

[HankScorpio2]

Hi

I want to import a duration from excel for Shrinkage.

This is a snapshot of the data. 

When I attempt to import it I get the date and time as it appears in the formula bar.

In Excel I set the field to a custom format [h]:mm:ss. However, this did not resolve the issue and the data still came into power query as a date and time. 

If I select "Duration" in PowerQuery then the transformation fails with an error. 

Have tried creating a new column from selection however, it only offers date based formats, no duration.

So then I have tried to create a custom column to transform the data using

= Table.AddColumn(#"Changed Type", "Scheduled Duration", each Duration.FromText([#"Scheduled Shrinkage (Time)"]))

However, this fails with a conversion error as well. 

How can I import the duration data and maintain its format?

EDIT

If i could do a datediff in PoweryQuery and minus 01/01/1900 that might work as well. I have tried this formula but unsure how to insert the date into the formula.

= Table.AddColumn(#"Removed Columns", "Scheduled Time", each Duration.TotalSeconds([#"Scheduled Shrinkage (Time)"] - "01/01/1900"))

In excel I can change the format by splitting the column by delimter ":" then multiplying to the correct unit.

This fails in PowerQuery, is there a way to split on ":" in PowerQuery?

My formula

= Table.AddColumn(#"Changed Type", "Scheduled Time", each TextSplitter.SplitTextByDelimiter([#"Scheduled Shrinkage (Time)"],":"))

Thanks, 

[dufoq3 · Accepted solution]

Hi @HankScorpio2, check this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31De0NDBQsLAyNrUC0gG+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Scheduled Schringage (Time)" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Scheduled Schringage (Time)", type datetime}}, "sk-SK"),
    Ad_Duration = Table.AddColumn(ChangedType, "Duration", each [#"Scheduled Schringage (Time)"] - #datetime(1900,1,1,0,0,0), type duration ),
    InsertedTotalSeconds = Table.AddColumn(Ad_Duration, "Total Seconds", each Duration.TotalSeconds([Duration]), type number)
in
    InsertedTotalSeconds

[dufoq3 · Accepted solution]

Hi @HankScorpio2, check this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31De0NDBQsLAyNrUC0gG+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Scheduled Schringage (Time)" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Scheduled Schringage (Time)", type datetime}}, "sk-SK"),
    Ad_Duration = Table.AddColumn(ChangedType, "Duration", each [#"Scheduled Schringage (Time)"] - #datetime(1900,1,1,0,0,0), type duration ),
    InsertedTotalSeconds = Table.AddColumn(Ad_Duration, "Total Seconds", each Duration.TotalSeconds([Duration]), type number)
in
    InsertedTotalSeconds

[WanderingBI]

I think the first step is to look at the input data:

5/1/1900 8:35:00 PM

This looks like a datetime format. The question would be where the 5 comes from?

Lets assume that the duration is in the time part of the datetime => 8:35:00 PM, then this would be 20 hours and 35 minutes.

First we can extract the time from the datetime (Power Query will detect your data as datatype automatically) by using Transform -> Time -> Time only. We then get values in the type time format:

Now we want to have values in a column that are of type duration. We can create duration like this:

#duration(days as number, hours as number, minutes as number, seconds as number) as duration

We then can add columns "add" -> "time" -> hours,minutes,seconds to get the three input parameters.

Full code example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31De0NDBQsLAyNrUC0gG+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type datetime}}),
    #"Extracted Time" = Table.TransformColumns(#"Changed Type",{{"Duration", DateTime.Time, type time}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Time",{{"Duration", "Time"}}),
    #"Inserted Hour" = Table.AddColumn(#"Renamed Columns", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Second", "Duration", each #duration(0, [Hour] as number, [Minute] as number, [Second] as number) as duration)
in
    #"Added Custom"

[HankScorpio2]

The result i want is 140:35:00 or some correct decimal conversion. 

Where does the 5 come from? I can only assume that there are 5 days in 140 hours. Which would leave 20hrs over. In 24hr time 20:00 is 8pm. And there was 35 minutes left over 8.35pm.

If I could get access to each value separately say 140 i could multiply that by 3600. the minutes by 60 and add it all together to get a decimal output in seconds.

[WanderingBI · Accepted solution]

Just took another look and I think this will work for all durations that are smaller than 31 days 23 hours 23 minutes and 59 seconds:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31De0NDBQsLAyNrUC0gG+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type datetime}}),
  #"Inserted date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Duration]), type nullable date),
  #"Inserted time" = Table.AddColumn(#"Inserted date", "Time", each DateTime.Time([Duration]), type nullable time),
  #"Inserted day" = Table.AddColumn(#"Inserted time", "Day", each Date.Day([Date]), type nullable number),
  #"Inserted hour" = Table.AddColumn(#"Inserted day", "Hour", each Time.Hour([Time]), type nullable Int64.Type),
  #"Inserted minute" = Table.AddColumn(#"Inserted hour", "Minute", each Time.Minute([Time]), type nullable Int64.Type),
  #"Inserted second" = Table.AddColumn(#"Inserted minute", "Second", each Time.Second([Time]), type nullable Int64.Type),
  #"Added custom" = Table.AddColumn(#"Inserted second", "DurationType", each #duration([Day], [Hour] as number, [Minute] as number, [Second] as number) as duration, type duration),
  #"Inserted multiplication" = Table.AddColumn(#"Added custom", "DayX24", each [Day] * 24, type number),
  #"Added custom 1" = Table.AddColumn(#"Inserted multiplication", "HoursTotal",  each [DayX24]+[Hour]),
  #"Inserted merged column" = Table.AddColumn(#"Added custom 1", "HH:MM:SS", each Text.Combine({Text.From([HoursTotal]), Text.From([Minute]), Text.From([Second])}, ":"), type text)
in
    #"Inserted merged column"

From a data point of view I would recommend to work with the exact duration data type though.