Sum of Time differences by two condition

[Rochejf]

Hi I really need some help here.

Referring to the image attached, I want to find the time difference in a logic of column TRANSACTION_STATUS(7-4). Probably this can be done in way with the latest timemark minus the second latest timemark then continue the loop. 

After that, all the time difference will be sum under BATCH_RUN_STEP_ID 12.

Thanks

[Rochejf · Accepted solution]

Thanks! 
The code works out perfectly.

However, after i swapped it to powerbi loaded database, it shows token identifier expected. How should i amend the code?

let
    Source = BATCH_RUN_STEP_LABOR_TRANSACTION, let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TRANSACATION_DATE_TIME = _t, TRANSACTION_STATUS = _t, BATCH_RUN_STEP_LABOR_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source, {
        {"ID", Int64.Type},
        {"TRANSACATION_DATE_TIME", type datetime},
        {"TRANSACTION_STATUS", Int64.Type},
        {"BATCH_RUN_STEP_LABOR_ID", Int64.Type}
    }, "es"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Spent", each
        if [TRANSACTION_STATUS] = 7 then
            [TRANSACATION_DATE_TIME] -
            Table.Sort(
                Table.SelectRows(
                    #"Changed Type",
                    (k) => k[TRANSACTION_STATUS] = 4 and k[TRANSACATION_DATE_TIME] < [TRANSACATION_DATE_TIME]
                ),
                {{"TRANSACATION_DATE_TIME", Order.Descending}}
            ){0}[TRANSACATION_DATE_TIME]
        else null,
        type duration
    ),
    #"Grouped Rows" = ""
in
    #"Grouped Rows"

Hope you can assist me again on this manner

[lbendlin]

your #"Grouped Rows"  step is empty/missing.

[Rochejf]

Thanks!

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Rochejf]

Sorry for the inconvenience, let me try this again.

ID	TRANSACATION_DATE_TIME	TRANSACTION_STATUS	BATCH_RUN_STEP_LABOR_ID
2	26-05-22 13:18:55	4	12
4	26-05-22 13:19:43	4	12
6	26-05-22 13:19:57	4	12
3	26-05-22 13:19:41	7	12
5	26-05-22 13:19:48	7	12
7	26-05-22 13:19:58	7	12
42185	18-01-23 15:28:38	7	12903
42170	18-01-23 12:59:01	4	12903

According to the table attached, there are 4 sets of running times under 2 groups, group 1 and group 2.

I wish to obtain the time difference and the sum separate by groups. However, the time recorded may not always be in sequence. Therefore, I wish there is a method to perform the calculation starting with the latest IN and OUT time in each group the follows.

The formula for the ideal outcome would be:

GROUP 1 = sum((ID7-ID6)+(ID5-ID4)+(ID3-ID2)) = 52 sec GROUP 2 = ID42185-ID42170 = 8977 sec

The sample table:

Time Spent	GROUP
52	1
8977	2

Really hope to solve this thanks!

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5hCoAwCAXgq8R+N/DpbM6rjO5/jVZQYe2HII+Pp70nTmviLZNm5gXiMFcdWRkDTvvarzWQ5kUi2f5EayQyacHI6kt0QiySOjn0IYVhZxMsEzLLAnU2l5c1kltWCpJdmxOezy+5Hw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TRANSACATION_DATE_TIME = _t, TRANSACTION_STATUS = _t, BATCH_RUN_STEP_LABOR_ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"TRANSACATION_DATE_TIME", type datetime}, {"TRANSACTION_STATUS", Int64.Type}, {"BATCH_RUN_STEP_LABOR_ID", Int64.Type}},"es"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Spent", each if [TRANSACTION_STATUS]=7 then [TRANSACATION_DATE_TIME]-Table.Sort(Table.SelectRows(#"Changed Type",(k)=>k[TRANSACTION_STATUS]=4 and k[TRANSACATION_DATE_TIME]<[TRANSACATION_DATE_TIME]),{{"TRANSACATION_DATE_TIME",Order.Descending}}){0}[TRANSACATION_DATE_TIME] else null,type duration),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"BATCH_RUN_STEP_LABOR_ID"}, {{"Time Spent", each List.Sum([Time Spent]), type duration}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".