Get arrival time from previous row add 5 minutes and add to next row depart time

[Pandadev]

Hi I have a table of data with blanks that need to be filled in from the previous row

The original table looks like this

Origin                 Destination           Depart        Arrival             Code 
Kodiak                Kitoi Bay               11:00AM     11:25AM         501 
                           Seal Bay                                   11:45AM         511
                           Port William                             12:25PM         521

I need to fill in the blanks Origin by adding the previous destination and then I need to fill in the blank depart by previous arrival + 5 mins

This is how the output should look

Origin                 Destination           Depart        Arrival             Code 
Kodiak                Kitoi Bay               11:00AM     11:25AM         501 
Kitoi Bay             Seal Bay                11:30AM     11:45AM         511
Seal Bay             Port William          11:50AM     12:25PM         521

[AlexanderPrime · Accepted solution]

Assuming your table is called Table1 (Replace Table1 with your table name in all the code below)

IN POWER QUERY (Transform Data)

First you'll need to make an index column within PowerQuery/Transform Data so DAX will know how to declare an order.

Then add a custom column, call it NextDepart and add the following code

[Arrival] + #duration(0,0,5,0)

Then set this new column to be in the "TIME" data format.

Exit out of Power Query, (close and apply)
IN DAX

Make 2 columns to replace Origin and Depart with the following DAX code:

Origin2 =

VAR PreviousDestination = CALCULATE(MAX(Table1[Destination]),FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1))

RETURN

IF((Table1[Origin]=""),PreviousDestination,Table1[Origin])

Depart2 = VAR PreviousArrival = CALCULATE(MAX(Table1[NextDepart]),FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1))

RETURN

IF(ISBLANK(Table1[Depart]),(PreviousArrival),Table1[Depart])

Note: For Origin2. If it's not populating correctly. replace IF((Table1[Origin]="") in the code with (ISBLANK(Table1[Origin])

[Pandadev]

Thankyou , the code worked perfectly.