Forum Discussion
Solved
Timestamp based on specific criteria
Hello, I have requirement regarding Power Query for creating time stamp and a new table having all the times. I have a variable with numbers of seconds after midnight. But it exceeds 24:00 (e.g....
1. Use below
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY1NDBQUIrViVayNDcyMACzzI2MzSBCpgYgoVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"time", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "timestamp", each [t=[time]/3600, h=Text.PadStart(Text.From(Number.IntegerDivide(t,1)),2,"0"), m=Text.PadStart(Text.From(Number.Round(60*Number.Mod(t,1),0)),2,"0"), T=h&":"&m][T]) in #"Added Custom"2. A table with all minutes between 00:00 and 30:00 (though I have used minutes and second to generate the table but its output is 00:00 to 30:00 only which you want)
let ListOfTimes = Table.FromList(List.Transform(List.Times(#time(0,0,0),1801,#duration(0,0,0,1)),(x)=>Time.ToText(x,"mm:ss")), null, {"Time"}) in ListOfTimes
This alternative,
1) Uses Number.Mod to find the number of seconds left after removing the days.
2) Uses #duration to convert that number of seconds into a duration.
3) Adds that duration to 00:00:00 to get a time.
4) Returns as type text (as required by the original post – but could be returned as a type time).
= Table.AddColumn(
#"Name of Previous Step",
"Time",
each #time(0,0,0) + #duration( 0, 0, 0, Number.Mod([time],86400) ),
type text
)