Calculate Age between Duplicates

[Anonymous]

I have a table with ticket data where each ticket appears twice - one with the oldest date/time an event occurred, and one with the newest date/time an event occurred. They are labelled in a column as either "Outgoing" (oldest), or "Incoming" (newest). 

I want to add a column that calculates, per each pair of ticket IDs, the timestamp of the Incoming minus the timestamp of the Outgoing to determine the amount of time between the two (how long it took to complete this event lifecycle).

Here is an example of the data layout, with the desired AGE column in double quotes:

TICKET_ID, TIMESTAMP, DIRECTION, AGE

TicketA, 01/02/2023 12:00, Incoming, "1.5"

TicketA, 01/01/2023 00:00, Outgoing, ""

TicketB, 02/10/2023 13:00, Incoming, "2"

TicketB, 02/08/2023 13:00, Outgoing, ""

Outgoing does not require the calculation, only Incoming, to determine how long it took to complete the event. The data is sorted so that the matching tickets are together, with Incoming on top and Outgoing below it.

In Excel I could use a formula like this, and then after I can just ignore the Outgoing rows as Incoming will have the AGE field I need:

=IF(A2=A3, B2-B3, " ")

How can I go about this in Power Query?

[vicky_ · Accepted solution]

One way of doing it would be to change the data structure in power query using the pivot functions. 

So you would have something like 
Ticket | Incoming Time | Outgoing time | (calculated) time difference.

If you need to keep the data the same, then you could create a calculated column like:
Age = 

var outgoingTime = CALCULATE(MAX([Timestamp]), FILTER(ALL([TableName]), [TICKET_ID] = EARLIER([TICKET_ID]) && [DIRECTION] = "Outgoing")

return DATEDIFF(outgoingTime, [TIMESTAMP], DAY)

(note that this functio returns a whole number representing the whole days between the two dates)

[Anonymous]

Hi Vicky,

Thanks for your reply.

I think you are right, I need to use Pivot to get a single row with both timestamps as you described.

I am trying to do that, but when I pivot, I still have two rows per Ticket ID. I now have two new columns of each DIRECTION, Incoming and Outgoing, so I think i'm on the right track. The question now is how can I get the value from every second row's Outgoing column to fill the null in the row above it?

Example of one "pair" of a single ticket:

[Anonymous]

Found a solution to this last part using "Fill Up", and then removed duplicates as they were no longer needed.