Generate timestamp for each hour between range

[Sclark]

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!

[MarcelBeug · Accepted solution]

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
    RemovedColumns

[v-jiascu-msft · Accepted solution]

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

[MarcelBeug · Accepted solution]

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
    RemovedColumns

[Anonymous]

is this possible on direct query?

[Sclark]

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?

[MarcelBeug]

You only need DAX if it is a calculated table.

Otherwise you can use the query above; just adjust the data source to yours.

[Sclark]

I'll give this a shot. Thank you!