Forum Discussion
Generate timestamp for each hour between range
I am looking to generate a timestamp for each hour between a range.
Columns: Ticket open [date time], Ticket suspended [date time], Ticket reactivated [date time], Ticket closed [date time]
Example data: Ticket ID: 12345. Ticket open = 1/1/2017 1:15 PM, Ticket suspended = 1/1/2017 4:36 PM. Ticket reactivated = 1/1/2017 6:11 PM, Ticket closed = 1/1/2017 8:45 PM.
Desired output: A record for each hour Ticket ID: 12345 was active. Then I can plot this activity on a heat map. I am not concerned with minutes.
1/1/2017 1:00 PM
1/1/2017 2:00 PM
1/1/2017 3:00 PM
1/1/2017 4:00 PM
1/1/2017 6:00 PM
1/1/2017 7:00 PM
1/1/2017 8:00 PM
If someone could point me in the right direction, I would really appreciate it. Thanks!
Example In Power Query:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Ticket open", type datetime}, {"Ticket suspended", type datetime}, {"Ticket reactivated", type datetime}, {"Ticket closed", type datetime}}), DateAndHourTimes24 = Table.TransformColumns(#"Changed Type",{{"Ticket open", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}, {"Ticket suspended", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}, {"Ticket reactivated", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}, {"Ticket closed", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}}), AddedHourOpen = Table.AddColumn(DateAndHourTimes24, "Hour Open", each List.Transform({[Ticket open]..[Ticket suspended],[Ticket reactivated]..[Ticket closed]}, each DateTime.From(_ / 24)), type {datetime}), ExpandedHourOpen = Table.ExpandListColumn(AddedHourOpen, "Hour Open"), RemovedColumns = Table.RemoveColumns(ExpandedHourOpen,{"Ticket open", "Ticket suspended", "Ticket reactivated", "Ticket closed"}) in RemovedColumnsHi Sclark,
Query code could be the best solution. Here is a DAX solution, which is ugly.
1. Create a datetime table.
TimeTable = GENERATESERIES ( DATEVALUE ( MIN ( 'Ticket'[Open] ) ), DATEVALUE ( MAX ( 'Ticket'[Closed] ) + 1 ), TIME ( 1, 0, 0 ) )2. Create a result table.
Result = FILTER ( CROSSJOIN ( TimeTable, Ticket ), [Value] > [Open] - TIME ( 1, 0, 0 ) && [Value] <= [Suspended] || [Value] > [Reactivated] - TIME ( 1, 0, 0 ) && [Value] <= [Closed] )3. Delete the columns if needed.
Please reference this file: https://1drv.ms/u/s!ArTqPk2pu-BkgRZJFof6N-uug1Jw
Best Regards!
Dale
6 Replies
Example In Power Query:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticket ID", Int64.Type}, {"Ticket open", type datetime}, {"Ticket suspended", type datetime}, {"Ticket reactivated", type datetime}, {"Ticket closed", type datetime}}), DateAndHourTimes24 = Table.TransformColumns(#"Changed Type",{{"Ticket open", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}, {"Ticket suspended", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}, {"Ticket reactivated", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}, {"Ticket closed", each 24 * Number.From(DateTime.Date(_) & #time(Time.Hour(DateTime.Time(_)),0,0))}}), AddedHourOpen = Table.AddColumn(DateAndHourTimes24, "Hour Open", each List.Transform({[Ticket open]..[Ticket suspended],[Ticket reactivated]..[Ticket closed]}, each DateTime.From(_ / 24)), type {datetime}), ExpandedHourOpen = Table.ExpandListColumn(AddedHourOpen, "Hour Open"), RemovedColumns = Table.RemoveColumns(ExpandedHourOpen,{"Ticket open", "Ticket suspended", "Ticket reactivated", "Ticket closed"}) in RemovedColumnsI'll give this a shot. Thank you!
I should have specified this in my initial post. Is there a DAX query that I could use, since I already have all of this data in a table in power BI?
You only need DAX if it is a calculated table.
Otherwise you can use the query above; just adjust the data source to yours.
Hi Sclark,
Query code could be the best solution. Here is a DAX solution, which is ugly.
1. Create a datetime table.
TimeTable = GENERATESERIES ( DATEVALUE ( MIN ( 'Ticket'[Open] ) ), DATEVALUE ( MAX ( 'Ticket'[Closed] ) + 1 ), TIME ( 1, 0, 0 ) )2. Create a result table.
Result = FILTER ( CROSSJOIN ( TimeTable, Ticket ), [Value] > [Open] - TIME ( 1, 0, 0 ) && [Value] <= [Suspended] || [Value] > [Reactivated] - TIME ( 1, 0, 0 ) && [Value] <= [Closed] )3. Delete the columns if needed.
Please reference this file: https://1drv.ms/u/s!ArTqPk2pu-BkgRZJFof6N-uug1Jw
Best Regards!
Dale
