Create new table to show time between orders

[Pick]

Hi,

  I have a table with a list of orders with start date and end time.  I need to produce another table to show the time in minutes from the end of the last order to the strat of the next order.  Before and after tables are shown below.  Any help is appreciated.

BEFORE

Sono	Organizational_Unit	Order_No	Product_Code	Week	Start_Time	End_Time	Minutes
Sono2	Hair	13245860	9118496	1	02/01/2020 15:07	02/01/2020 19:21	254.25
Sono2	Hair	13245861	9118496	1	02/01/2020 19:52	02/01/2020 23:10	198
Sono2	Hair	13247645	67836033	1	03/01/2020 00:04	03/01/2020 02:24	139.75
Sono2	Hair	13247646	67836033	1	03/01/2020 02:19	03/01/2020 05:21	181.75

AFTER

Sono	Organizational_Unit	Previous_Order_No	Previois_Product_Code	Next_Order_No	Next_Product_Code	Previous_Order_End_Time	Next_Order_Start_Time	Minutes
Sono2	Hair	13245860	9118496	13245861	9118496	02/01/2020 19:21	02/01/2020 19:52	21
Sono2	Hair	13245861	9118496	13247645	67836033	02/01/2020 23:10	03/01/2020 00:04	54
Sono2	Hair	13247645	67836033	13247646	67836033	03/01/2020 02:24	03/01/2020 02:19	-5
Sono2	Hair	13247646	67836033	13247849	9118516	03/01/2020 05:21	03/01/2020 06:27	76

  Regards

   Ian

[dax · Accepted solution]

Hi @Pick , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pick]

@dax that worked thank you

[BA_Pete]

Hi @Pick ,

In Power Query, sort [start_time] and [end_time] so that they show correctly in chronological order.

Add an index column starting from 0, call it [index0] or similar.

Add index column starting from 1, call it [index1] or similar.

Merge (join) the table on itself using [index0] as the first join column and [index1] as the second.

Expand the [time_start] and [time_end] columns and rename them to [prev_time_end] etc.

EDIT:

Here's the Power Query code to follow the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZAxCsMwDEWvUjyHRPq2ZEsn6N4xZOjYpYHef6id0kINziAkHtLjo3UNt/25I0zhen+8auOIJEWpjsZckmmDtQgL8QICXViccofM0bYgaYaEbRqJ+VRsLvhHiM4tDFsZWbMmqaPmEpVi/Grjz0HklDoERzrubc7DvNWs52Y4W4fk8wkufJi3Nw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sono = _t, Organizational_Unit = _t, Order_No = _t, Product_Code = _t, Week = _t, Start_Time = _t, End_Time = _t, Minutes = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"Sono", type text}, {"Organizational_Unit", type text}, {"Order_No", type text}, {"Product_Code", type text}, {"Week", type text}, {"Start_Time", type datetime}, {"End_Time", type datetime}, {"Minutes", type number}}),
    sortDateTimes = Table.Sort(chgAllTypes,{{"Start_Time", Order.Ascending}, {"End_Time", Order.Ascending}}),
    addIndex0 = Table.AddIndexColumn(sortDateTimes, "Index0", 0, 1),
    addIndex1 = Table.AddIndexColumn(addIndex0, "Index1", 1, 1),
    mergeOnMyself = Table.NestedJoin(addIndex1, {"Index0"}, addIndex1, {"Index1"}, "Added Index1", JoinKind.LeftOuter),
    expandPrevTimes = Table.ExpandTableColumn(mergeOnMyself, "Added Index1", {"Start_Time", "End_Time"}, {"Prev_Start_Time", "Prev_End_Time"})
in
    expandPrevTimes

In Power Query, go to New Source > Blank Query, then in Advanced Editor paste my code over the default code.

I've only expanded the DateTime columns in my example, the principle is the same for any other columns you want to show the previous value of.

Pete