cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Junaid11
Helper IV
Helper IV

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. 95000/3600 = 26.389). The value after 24 needs to be retained. So based on the current variable, a new variable should be created, which contains the timestamp in text, like: 26:23.

 

Format would be hh:mm. So the values need to have zeros if necessary necessary. E.g. 2:2 should be 02:02.

 

Anothe example

35100 / 3600 = 9.75

9.75 should be 09:45

 

I have attached sample query for the data set below as well:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjY1NDBQUNJRUorViVayNDcyMIBxzI2MzcDsWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [time = _t, timestamp = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"time", Int64.Type}, {"timestamp", type text}})
in
#"Changed Type"

 

A new table query that should contain all times between 00:00 and 30:00. Variables: timestamp (e.g. 12:33), hours (e.g. 12), minutes (e.g. 33) and timerange in hours: (e.g. 12:00 - 12:59). 

The other requirement for a table with all minutes between 00:00 and 30:00 (automatically generated in Power BI). E.g.
00:00
00:01
00:02
... up to ...
29:58
29:59
30:00

Your help would be highly appreciated.

Thank you

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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

 

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

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

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors