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