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

[lucasneedhelp]

Hi All,

I'm have a set of data already formated and sorted by space and start time. I'm trying to add a column to say if space is the same as previous row, calculate start time from current row - end time from previous row. otherwise leave blank. Can someone please help with the fomular? Thanks heaps!! Below is an example of the data set :

SPACE	START TIME	END TIME
ABC	21/06/2025 06:00:00	22/06/2025 23:59:00
ABC	25/06/2025 00:01:00	26/06/2025 23:59:00
DEF	30/11/2023 00:01:00	01/12/2023 06:00:00

[BA_Pete · Accepted solution]

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

[AlienSx · Accepted solution]

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))

 

[lucasneedhelp]

Hi Alien, 

thanks for your solution, i'll give it a go. we haev multiple spaces for hire int he venue, and i'm just trying to figure out the turnaround time for a space between last hire and next hire.

[BA_Pete · Accepted solution]

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

[lucasneedhelp]

Hey Pete, this works perfectly for me!

Brilliant!!!!!

[lucasneedhelp]

Thanks Pete. the script seems a bit too advanced for me. but i'll give it a go.