Forum Discussion

Winata_Saputra's avatar
Winata_Saputra
Frequent Visitor
3 years ago
Solved

How To Create a Start Time and Finish Time Based on Out Time and Index Using Power Query

Hello everyone. I've been trying to find a solution to my timing issue, but still not getting the expected results. Let me explain.

I have 3 important columns (INDEX, PRODDATE, MACHINE) and 1 reference column which is OUTTIME.

There are only 2 conditions:

  • If PRODDATE and MACHINE are the same, which has INDEX starting at 1, then STARTPRODTIME, starting at 07:00 and FINISHPRODTIME ending the next day at 07:00 (like the desired column in picture).
  • If PRODDATE and MACHINE are the same, but have INDEX of more than 1, then STARTPRODTIME and FINISIHPRODTIME refer to the OUTIME column (like the desired column in picture).

Is this possible?

Please see my picture for better understanding.

Desired Result

 Thankyou!

Need Help
Tips and Tricks
Tutorial Requests
  • slorin's avatar
    slorin
    3 years ago

    Hi

     

    Table.Combine(
        Table.Group(
         #"Previous Step",
         {"INDEX"}, 
         {{"Data", each Table.FromColumns(
             Table.ToColumns(_) &
               {{[PRODDATE]{0} & #time(7,0,0)} & List.RemoveLastN([OUTTIME],1)} &
                {List.RemoveLastN([OUTTIME],1) & {Date.AddDays([PRODDATE]{0},1) & #time(7,0,0)}},
             Table.ColumnNames(_) & {"STARTPRODTIME","FINISHPRODTIME"})
         }},
        GroupKind.Local,
        (x,y) => if y[INDEX]=1 then 1 else 0)
    [Data])

     

    Stéphane

2 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    Hi

     

    Table.Combine(
        Table.Group(
         #"Previous Step",
         {"INDEX"}, 
         {{"Data", each Table.FromColumns(
             Table.ToColumns(_) &
               {{[PRODDATE]{0} & #time(7,0,0)} & List.RemoveLastN([OUTTIME],1)} &
                {List.RemoveLastN([OUTTIME],1) & {Date.AddDays([PRODDATE]{0},1) & #time(7,0,0)}},
             Table.ColumnNames(_) & {"STARTPRODTIME","FINISHPRODTIME"})
         }},
        GroupKind.Local,
        (x,y) => if y[INDEX]=1 then 1 else 0)
    [Data])

     

    Stéphane