Turnaround times between two columns and different rows with multiple variables

 

Yep, that's worked, thanks.

 

Try this example query to get the previous Theatre Out time on the next row:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZq7juU2DIZfZeF6gxF1l7spAqTJBUiAFIvBNilSJWVeP5JtUZT1H43VHGAI+DuUSf6keObbt+3Hf/7698vvf/70w3/fdXhT9k0rbb6//7x93drfX1TYnduVulnjbsNpdTp/bB9fHwMjBKad7GmllD/iCjE/GxHRptNqKH/YNaIzA5HUbq5DW7tIzM9aNRJpV6Fai6MrxPxsQkRDl7UERk+Iv92JZifgo9mNFsQ1oBlDneNsrq9x7pNDD8T8rEVE54WLs8AMxOLgSHTso1HrRONHYjZVz2P+8CvE/OyY4NmqgyiZ08c//v5CI/GXX2/Phl3rkZi/5LJaVUOdgfpRVQMXc4Lq61WEuAjM5QtKkHa6kj75cvCwgCy1AdIxf8tlTce79BMkyHANvHQ7XccmMuUzrjDdrmG0q5umSAXpijQPkDmqwE3dNJdEdJ4BdUBAe/lIJdxkVpBYfexuvQi4ditIi1pNiY4RXqYVYo4NodiYixg0N6+HRA8bQ+BKPBQtDGoRb8TYJXTVn85qr6yyWCOnRGdHomWdO4ijok2Itp06yrfLGokb7IToWvp01q55LfkodLyz1smH8FgxJfJY0Vtrlq666LmCe2v9GpPO7nDvDA35fkNmFWeRFEi1u0t36Zh9JsTBSQ1jrXcXRRWuAC100TWBvFRyAem4r9wic1W70cfRBxmfvMjUBovO2g0Wa8A6fvaRqXlvDCvFM6J4trcaWYVqjWjiSKTWfpycVJ4QicPaWw1X0lbH3MdE5KNuiltO7VaIug0/nZXfo+fCxkSk4SAfTSMek/PMSYBE6WPF693qMP6QaOGx23h/SO4iEbcFkjVDs4xElQ0k0rUcsJ9kJCBaKLp8lTOr0fbtAiusgYdnH8928xp5Dvjt4Tzd6/FN5llVX657qeLmUV+wGskZV41aBCZU2GXsr3fsLnueER0oGsUjwCGQYHCe6hlKSKFnBs57U/VB+ai5aILvYg2QqLKBk5bP7bWU8SdEy4P8rZRICBoY76dVo8G5w071xnCMUzTKRboleWoBV61uOmuddHGLTbfgpC7JaQSmFm69CEx81+itXEsJC9oUacdDl1Yut12j/Lwm5mc1IOYXQaJu0gqROKy91cnaBuNUuuVPQhXSW7nk0zw0A9C2K2zqcvzy3MazKd6QRvXI9veR475mnlT3mlTBtdoWEz69KSeIiv+Wc0BvrVsHYhl/yquiIK2Wv+XYLaQFoOUEl9a2VLNhA5ekCdBxR+mtWvaZYa05BVbh6a1Wrgy5KdAAfL8Bs0hocGR12/k8B5YO5UYgcX4eK3YaCnDqohqJZfNeo+JAsUx4CUY5H/kKiktnL3xMLJqj4Jkvv72TjXUEDmHWLGK3Srky29MGhtFpqViQiL4tjxxuWBOkZ5HurU3QkXQ34NEA5aOBJ2NpjazcjqZxHniRIyqtib/FoS3uhJdQlLOpaqyTeWgelR44r+essZGBcoNyi4iWQa4bp95abwcG6euUVwen3upYGza0zXsNtNBBu7u6qI8b+rVsBnQA6Np1wSN9nQAdl1hv5TVZW7I+BdbDSWurneOnMrcA9E2wO2tdQJluKUED8P0GFHchCVQ8JkZZdp/zynYtjrw88dSdm6KzWF4iUWIHmDcVSXrqIwqzGoGBmysdF2lNr5GnOoiHRZcS1sTqENTUxYGX+LImrNnr+hodHBGnYfYDr0xKVa7hbeA1L/H9qbfyFTOhFjUFggOXtlzXv2UQGS8Xs0QkkDWqXVfw+m4GtKD0qF0kj83GArDdLHqrlTfTFQ8JeqjbO2zzJgQOhaJ59u2tLLDwUjqrZAL6atoSCs1yU54bs7rM8bWQj5CsnLjt43t9rXWSTKcMD4jtp+1ebKrAmmP/MF5JX2tDgPIV+d1+og1AuwxoAeJ/W8waL/GPdr2V19xhlWfHN6jb1G3W/CtPjjHO1nqj0GiSm2or0H7x/wIR/iI9lcJRW8seq+7+jhZK48/Hs46nQFduc0igU28+Pv4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Schedule ID" = _t, #"Theatre In Date/Time" = _t, #"Theatre Out Date/Time" = _t, #"TH Duration" = _t, #"Turnaround Time(minutes)" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Schedule ID", type text}, {"Theatre In Date/Time", type datetime}, {"Theatre Out Date/Time", type datetime}, {"TH Duration", Int64.Type}, {"Turnaround Time(minutes)", Int64.Type}}),

// Relevant steps ----->
    sortScheduleTimeIn = Table.Sort(chgTypes,{{"Schedule ID", Order.Ascending}, {"Theatre In Date/Time", Order.Ascending}}),
    addIndex0 = Table.AddIndexColumn(sortScheduleTimeIn, "Index0", 0, 1, Int64.Type),
    addIndex1 = Table.AddIndexColumn(addIndex0, "Index1", 1, 1, Int64.Type),
    mergeSelfScheduleIndex = Table.NestedJoin(addIndex1, {"Schedule ID", "Index0"}, addIndex1, {"Schedule ID", "Index1"}, "addIndex1", JoinKind.LeftOuter),
    expandTheatreOut = Table.ExpandTableColumn(mergeSelfScheduleIndex, "addIndex1", {"Theatre Out Date/Time"}, {"prevTheatreOutDateTime"}),
    addTurnaroundMins = Table.AddColumn(expandTheatreOut, "turnaroundMinutes", each Duration.TotalMinutes([#"Theatre In Date/Time"] - [prevTheatreOutDateTime]), Int64.Type)
    
in
    addTurnaroundMins

 

Summary:

-1- Sort data on [Schedule ID] and [Theatre In Date/Time].

-2- Add two index columns with start value offset by 1.

-3- Merge table with itself on [Schedule ID] and [Index0] = [Schedule ID] and [Index1].

-4- Expand [Theatre Out Date/Time] and add custom column to calculate turnaround time.

 

Example query output:

 

 

Pete