Retrieve duration

[Anonymous]

Hello everyone, 

I have a table with date and time, Status and a flow ID. I want to calculate the duration between the first appearance of the Status 'Delivered' and the Status 'Stop flow'. 

I think the first step is to transpose the column, but since there are more statussen with 'Delivered' for one flow ID, I don't know how to do this. 

The second step is to calculate the difference as a number between the two new columns and finally create a measure to convert that number to a date and time function. I know those steps, so it's only the first step. 

So, the duration between Delivered and Stop flow for the first Flow ID should be 5 minutes and 5 seconds, and for the second Flow ID 5 minutes and 4 seconds.

If necesseary, you can use the link below; it containts the same sample data as in the image.

https://docs.google.com/spreadsheets/d/1rY_bxfziDo1lIjm0ssM_56UkGlWifLQi/edit?usp=sharing&ouid=11807...

[bolfri · Accepted solution]

Let's assume that your oryginal data is named: Sample.

1. Create a new blank query and create an aggregated table per Flow execution id, status with new Columns "min and max".

= Table.Group(Sample, {"Flow execution id", "Status"}, {{"min", each List.Min([Date and time]), type nullable datetime}, {"max", each List.Max([Date and time]), type nullable datetime}})

2. Group it once again packing this information into table.

3. Add custom column with Startdatetime:

List.Min(Table.Column(Table.SelectRows([Combined], each [Status]="Delivered"),"Date and time"))

4. Add custom column with End datetime

List.Max(Table.Column(Table.SelectRows([Combined], each [Status]="Stop flow"),"Date and time"))

5. Add duration

[End datetime] - [Start datetime]

Full code:

let
    Source = Table.Group(Sample, {"Flow execution id"}, {{"Combined", each _, type table [Date and time=nullable datetime, Flow execution id=nullable text, Status=nullable text]}}),
    #"Added Start Datetime" = Table.AddColumn(Source, "Start datetime", each List.Min(Table.Column(Table.SelectRows([Combined], each [Status]="Delivered"),"Date and time"))),
    #"Added End Datetime" = Table.AddColumn(#"Added Start Datetime", "End datetime", each List.Max(Table.Column(Table.SelectRows([Combined], each [Status]="Stop flow"),"Date and time"))),
    #"Added Duration" = Table.AddColumn(#"Added End Datetime", "Duration", each [End datetime] - [Start datetime])
in
    #"Added Duration"

[Anonymous]

@bolfri 
Thank you so much!! Works perfectly 🙂 I even have the Duration in a Time format immediatly, which I didn't expect. 

[andhiii079845]

If more delivered appear for own ID, do you want to take the earlist event?