Forum Discussion
Cannot import Duration from Excel
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 InsertedTotalSecondsJust 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.
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"
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.
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.