Calculate DateTime Diff Between Rows with PowerQuery

[Anonymous]

Hey all,

PreText:

Long story short, I am trying to replicate reports from a ticketing system using PowerQuery in Excel 2016.  I have been able to replicate all metrics from the ticketing systems front-end reporting service, except for one key metric.

I'm trying to keep the workbook fairly small in size, so that it doesn't take long to load the workbook.  For this reason I am trying to do the majority of work in Connection Only Power Queries, while transferring only the minimal amount of data necessary for reporting, usually in some aggregate format to data models for reporting.

The challenge:

I need a way to calculate the age of a ticket as it changes through different status' (Request -> Open -> Work in Progress -> Closed).  The problem is that the table which tracks status changes (FieldHistory) only captures the DateTimeStamp that the status changes and NOT the DateTimeStamp that the status ends, unlike other tables with the database.  So, the only way I can calculate the DateTime difference is to do so between rows.

Some special situations to keep in mind for this dataset:

• Some tickets may be created as closed, (shows Null as previous value).  The age of these tickets should just be 0, because they were closed without additional work required.  They actually come from phone tickets Tier 1 Help Desk resolved immediately.
• OPEN tickets won't have a follow-up value, and in those cases a datediff between the timestamp and NOW() would be required to calculate time to the current date.  So essentially, the last value of every ticket should always count between the DateTimeStamp and NOW()

Completing these calculations per row (Per status) will allow me to pivot out specific status' management doesn't want to include in the total age.  For instance, in addition to time in Closed status, they may want to exclude time in "Waiting for Response", which is a status for when IT is waiting on the user to provide some info and somewhat out of IT's control.

Is there any way this can be accomplished in Power Query using M-Code or a Function?  

Another idea I had but don't know how to implement is the creation of a custom column using M-Code or a function which 'simply' captures the next status date (or null if none exists) for each ticket and then I create a separate column to then calculate the DateTime difference between the DateTimeStamp and custom column.  Whichever method would provide a solution, I am up for either.

Thank you, in advance!

Sincerely,

Kristopher

[Stachu]

have a read here, it should point you in the right direction
https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

[Anonymous]

Hey @Stachu ,

I did something sorta like this last night which seems to be working out in the right direction, but now I am having another issue with calculating the datetime difference (in minutes) between values based on an if statement.

I sorted the dataset by Ticket # in descending order, then by sequence in ascending order.  Then I added two index columns, one starting at 0 and another starting at 1.  Then I merged the table with itself so I had the following row beside of every entry.  

So, now I had the data in the order I needed for DateDiff type of calculations, but when I tried to perform the calculation I received the errors throughout the whole column.  Any advice?

= Table.AddColumn(#"Expanded Added Index1", "TimeInStatus", each if [TicketNumber] = [Added Index1.TicketNumber] then Duration.Minutes([Added Index1.DateTimeStamp],[DateTimeStamp]) else Duration.Minutes(DateTime.LocalNow(),[DateTimeStamp]))

Essentially, if the incident numbers are the same across columns, then calculate the difference of time in minutes between the new timestamp and the DateTimeStamp of the current status, otherwise if the ticket number is NOT the same then do a calculation for the difference in time between NOW() and the DateTimeStamp.

Sincerely,

Kristopher

[Anonymous · Accepted solution]

@Stachu ,

Sorry, I think I figured it out.  The function works through subtraction of the values and not the provision of two separate values.  I replaced the commas with (-) and it works

= Table.AddColumn(#"Expanded Added Index1", "TimeInStatus", each if [TicketNumber] = [Added Index1.TicketNumber] then Duration.Minutes([Added Index1.DateTimeStamp] - [DateTimeStamp]) else Duration.Minutes(DateTime.LocalNow() - [DateTimeStamp]))

Sincerely