Calculated Column between start time from current row and end time from previous row

Hi lucasneedhelp ,

 

One way to do it is to create two offset Index columns and merge the table on itself using [SPACE] & [Index0] = [SPACE] & [Index1], like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjLUNzDTNzIwMlUwMLMyMAAikKgRXNTI2MrUEiQaqwPXY4rQA9RgCNVjhl2Pi6sbUNbYQN/QECRrjKzHwFDf0AgqCrM9NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SPACE = _t, #"START TIME" = _t, #"END TIME" = _t]),

// Relevant steps from here ====>
    addIndex1 = Table.AddIndexColumn(Source, "Index1", 1, 1, Int64.Type),
    addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
    mergeOnSelf = Table.NestedJoin(addIndex0, {"SPACE", "Index0"}, addIndex0, {"SPACE", "Index1"}, "addIndex0", JoinKind.LeftOuter),
    expandEndTime = Table.ExpandTableColumn(mergeOnSelf, "addIndex0", {"END TIME"}, {"END TIME PREV ROW"}),
// <==== Relevant steps end here

    sortIndex0 = Table.Sort(expandEndTime,{{"Index0", Order.Ascending}}),
    remOthCols = Table.SelectColumns(sortIndex0,{"SPACE", "START TIME", "END TIME", "END TIME PREV ROW"})
in
    remOthCols

 

To get this output:

 

 

Pete

hi, lucasneedhelp funny recursion

    f = (i, lst, space, etime) =>
        if rows{i}? = null 
            then lst 
            else 
                @f(
                    i + 1, 
                    lst & 
                        {rows{i} & 
                            [diff = 
                                if (rows{i}[SPACE] <> space or space = null) 
                                    then null 
                                    else rows{i}[START TIME] - etime
                            ]
                        },
                    rows{i}[SPACE],
                    rows{i}[END TIME]
                ),
    rows = List.Buffer(Table.ToRecords(your_table)),
    z = Table.FromRecords(f(0, {}, null, null))