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
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