M - how to split events ( action /reaction ) to calculate duration

[Kath_BE]

Hello,

I would like to get some help from the experts on how to do below in M

I have offers that get passed through an approval tool, generating events: 

I would need to calculate the duration between teh submision ( action) and the subsequent approval/rejection ( recation)

Issue ( for me) is that an offer can be reviseed muktiple times so I want find out in M how I can connect the reaction to the correct previous action in order o calculate whether the SLA of the approval was met

Thank you for the guidance

Herewith some sample data

ID	Event_owner	Event	Event_datetime
12	Tim	Submit	14/05/2020  14:49:10
12	Gerry	Approve	14/05/2020  14:52:22
12	Tim	Revise	14/05/2020  14:55:25
12	Tim	Submit	14/05/2020  14:57:04
12	Lindsay	Approve	14/05/2020  14:57:33
12	Tim	Revise	14/05/2020  15:00:00
12	Tim	Submit	14/05/2020  15:00:45
12	Gerry	Approve	14/05/2020  15:03:03
70	John	Submit	26/10/2020  12:33:52
70	Gerry	Reject	26/10/2020  14:07:43
70	John	Revise	26/10/2020  14:10:08
70	John	Submit	26/10/2020  14:13:35
70	Gerry	Approve	26/10/2020  14:16:54

thanks

[camargos88 · Accepted solution]

@Kath_BE ,

You can create a index column and a custom column like:

try
let 
_row =  #"Added Index"{[Index] - 1},
_ID = [ID],
_Event_datetime = [Event_datetime]
in

if _row[ID] = _ID then
 Duration.TotalMinutes(_Event_datetime - _row[Event_datetime])
else 
null otherwise null

[Jimmy801 · Accepted solution]

Hello @Kath_BE 

you can add a index-column and a new column. This approach assumes that on every submit-event a Approve or a reject-event happens

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFNC8IwDAbgvyI7D5amzaa5eRLE0/Q2dvCj4IR9sOnAf2+YzsqmMihtoQ/hTZoknkLP93ZZLvv2dsizq1yUCYACBITZTBk2C1bgpf4Lr2xd3+VcVlVdtnbsCRnR+Wfx2LZZ8w0TIw3xryQUMRiHN1lxavb/s0Ss9bQsxACypmXpsKHJUxGvZXU+Anldl+fiszqGgYJeo4SWMTrdV4/txR5H3DBEbMbF330OtJI+5xOjiNasaRzFNTr0IZN8UvoA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Event_owner = _t, Event = _t, Event_datetime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Event_owner", type text}, {"Event", type text}, {"Event_datetime", type datetime}}, "de-DE"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    AddDuration = Table.AddColumn
    (
        #"Added Index",
        "Duration minutes submit - approve ",
        (row)=> if row[Event]="Approve" or row[Event]="Reject" then Duration.TotalMinutes(row[#"Event_datetime"]- #"Added Index"{[Index= row[Index]-1]}[#"Event_datetime"]) else "",
        type number
    )
in
    AddDuration

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[camargos88 · Accepted solution]

@Kath_BE ,

You can create a index column and a custom column like:

try
let 
_row =  #"Added Index"{[Index] - 1},
_ID = [ID],
_Event_datetime = [Event_datetime]
in

if _row[ID] = _ID then
 Duration.TotalMinutes(_Event_datetime - _row[Event_datetime])
else 
null otherwise null