Forum Discussion

Rochejf's avatar
Rochejf
Frequent Visitor
3 years ago
Solved

Sum of Time differences by two condition

  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 t...
Need Help
Show and Tell
Tutorial Requests
  • Rochejf's avatar
    Rochejf
    3 years ago

    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

Rochejf's avatar
Rochejf
Frequent Visitor
3 years ago

Sorry for the inconvenience, let me try this again.

 

IDTRANSACATION_DATE_TIMETRANSACTION_STATUSBATCH_RUN_STEP_LABOR_ID
226-05-22 13:18:55412
426-05-22 13:19:43412
626-05-22 13:19:57412
326-05-22 13:19:41712
526-05-22 13:19:48712
726-05-22 13:19:58712
4218518-01-23 15:28:38712903
4217018-01-23 12:59:01412903

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 SpentGROUP
521
89772

 

Really hope to solve this thanks!

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago
    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".