Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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
Proud to be a Datanaut!
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |