cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sclark Frequent Visitor
Frequent Visitor

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!

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Generate timestamp for each hour between range

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
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Generate timestamp for each hour between range

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.

 

Generate timestamp for each hour between range.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please reference this file: https://1drv.ms/u/s!ArTqPk2pu-BkgRZJFof6N-uug1Jw

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
MarcelBeug Super Contributor
Super Contributor

Re: Generate timestamp for each hour between range

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
Specializing in Power Query Formula Language (M)

View solution in original post

Sclark Frequent Visitor
Frequent Visitor

Re: Generate timestamp for each hour between range

I'll give this a shot. Thank you!

Sclark Frequent Visitor
Frequent Visitor

Re: Generate timestamp for each hour between range

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

Re: Generate timestamp for each hour between range

You only need DAX if it is a calculated table.

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

Specializing in Power Query Formula Language (M)
Highlighted
Microsoft v-jiascu-msft
Microsoft

Re: Generate timestamp for each hour between range

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.

 

Generate timestamp for each hour between range.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Please reference this file: https://1drv.ms/u/s!ArTqPk2pu-BkgRZJFof6N-uug1Jw

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

pamboys09 Regular Visitor
Regular Visitor

Re: Generate timestamp for each hour between range

is this possible on direct query?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors