Forum Discussion
Helper IICreate new table to show time between orders
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
3 Replies
- BA_Pete
Super User
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 expandPrevTimesIn 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
