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 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

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

6 Replies

    • 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".

  • Rochejf's avatar
    Rochejf
    Frequent Visitor
    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