Time for item to reach certain status

[Anonymous]

Good afternoon!

I am trying to find a solution for below issue. I have 3 columns of data: date/time; Item and status. I would like to have a duration calculated in a query in the 4th column.

Every item starts with status ACK/UNACK, I would like to know for every item in the list how long it takes to reach status ACT/ACK. See below example of D43FC120, it took 3 minutes and 25 seconds to reach the status ACT/ACK. An item can occur more than once in the table so the function should only calculate the duration based on the first ACT/ACK it comes across in the table. If an item does not reach ACT/ACK it should display "no change".

Hope somebody can help, have been puzzling for a while now for a solution but not succesful yet.

If more info is needed please let me know!

Best regards,

Lucmev

Date	Item	Status	Time to ACK
1/1/2020 1:35:28 PM	D24LS303	INACT/UNACK
1/1/2020 1:35:29 PM	D24EC331	INACT/UNACK
1/1/2020 1:43:06 PM	D43FC120	ACT/UNACK	00:03:25
1/1/2020 1:43:32 PM	D43FC110	INACT/ACK
1/1/2020 1:46:31 PM	D43FC120	ACT/ACK
1/1/2020 1:48:22 AM	D44LS102	ACT/ACK
1/1/2020 1:48:26 AM	D44LS102	INACT/ACK
1/1/2020 1:49:41 PM	D24LS303	ACT/UNACK
1/1/2020 1:49:42 PM	D24EC331	ACT/UNACK

[CNENFRNL · Accepted solution]

Hi, @Anonymous , you may want to try a PQ solution as follows,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdA9CsMwDAXgqxjPAUtPrkm0BbeB0B8KbaeQ+1+jgpYQsONFaPiekLQsngMHEMixyknRu+fdd/6MeHsJibXzY8zv8LF69WtXBIYtcMki3A5EUUr/QJQpM8jaFhfsOdM2v8aTClen13CvgBt/2I5lQhunAh8vMmjk4pHHZxpH8cYdX78=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Item = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Item", type text}, {"Status", type text}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time to ACK",
        each
            if [Status] <> "ACT/UNACK" then ""
            else
                [
                    grouped = Table.Group(#"Changed Type", {"Item"}, {{"Time to ACK", each _}}){[Item = [Item]]}[#"Time to ACK"],
                    end = try grouped{[Status="ACT/ACK"]}[Date] otherwise null,
                    dur = end - [Date] ?? ""
                ][dur]
    )
in
    #"Added Custom"

Measure and calculated column solutions are also available, for fun😁

You may want to refer to the attach file for more details.

Measure:

Calculated column: