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

[Winata_Saputra]

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!

[slorin · Accepted solution]

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

[Winata_Saputra]

It works. Terima kasih for your help 😁🇮🇩