Calculating Time Between Start of One operation and End of the previous operations

%3CLINGO-SUB%20id%3D%22lingo-sub-1786643%22%20slang%3D%22en-US%22%3ECalculating%20Time%20Between%20Start%20of%20One%20operation%20and%20End%20of%20the%20previous%20operations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786643%22%20slang%3D%22en-US%22%3EI%20am%20trying%20to%20achieve%20this%20task%20using%20excel%20Power%20Query.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20do%20a%20transformation%20on%20a%20data%20set%20that%20has%20Work%20Orders.%20Within%20each%20Work%20Order%20there%20are%20different%20Operations.%20My%20task%20is%20to%20calculate%20the%20Inter-Operation%20Times.%20For%20that%20I%20need%20to%20calculate%20the%20time%20different%20between%20end%20of%20One%20operation%20to%20the%20start%20of%20the%20next%20operation%20for%20all%20the%20steps%20within%20that%20work%20order.%20Then%20Continuing%20doing%20this%20for%20rest%20of%20the%20work%20orders%20within%20the%20data%20set.%20I%20really%20do%20not%20know%20how%20do%20i%20manipulate%20that%20data%20to%20achieve%20this%20take.%3CBR%20%2F%3E%3CBR%20%2F%3EKindly%20advise%20if%20I%20should%20provide%20data%20set%20as%20well.%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1786643%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786775%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20Time%20Between%20Start%20of%20One%20operation%20and%20End%20of%20the%20previous%20operations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F834443%22%20target%3D%22_blank%22%3E%40KhanAK%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understood%20your%20question%2C%20you%20may%20follow%20these%20steps...%3C%2FP%3E%3COL%3E%3CLI%3EYou%20can%20create%20a%20Reference%20of%20the%20existing%20query.%3C%2FLI%3E%3CLI%3EThen%20insert%20an%20Index%20column%20starting%20from%201%20in%20the%20original%20query.%3C%2FLI%3E%3CLI%3EInsert%20an%20Index%20column%20starting%20from%200%20in%20the%20Referenced%20query.%3C%2FLI%3E%3CLI%3EThen%20merge%20both%20the%20queries%20on%20Index%20Columns%20and%20extract%20the%20Start%20Time%20Field%20from%20the%20reference%20query.%3C%2FLI%3E%3CLI%3ENow%20in%20the%20merged%20query%20you%20will%20have%20start%20time%20for%20one%20operation%20and%20start%20time%20of%20the%20next%20operation%20in%20the%20same%20row%20and%20you%20may%20then%20add%20a%20new%20column%20subtracting%20both%20the%20times.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1786800%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20Time%20Between%20Start%20of%20One%20operation%20and%20End%20of%20the%20previous%20operations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1786800%22%20slang%3D%22en-US%22%3EDear%20Subodh%3CBR%20%2F%3EThank%20you%20so%20much!%20I%20will%20test%20this%20solution%20and%20will%20update%20you%20shortly.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor
I am trying to achieve this task using excel Power Query.

I am trying to do a transformation on a data set that has Work Orders. So there are multiple Routing IDs. With each Routing ID there are one or More versions. For each version there are Multiple Work Orders. For each work order there are set of operations that are unique to the version ID. Now I need to:
Task 1: Find out wait times between these operations over all WO history
Task 2: Determine a simple average for the time between all Operations unique to the Version ID.

For task 1 I need to calculate the time difference between end of One operation to the start of the next operation for all the steps within that work order. Then Continuing doing this for rest of the work orders within the data set. I really do not know how To manipulate the data to achieve this take.

Kindly advise if I should provide data set as well. Thank you!
2 Replies
Highlighted

@KhanAK 

If I understood your question, you may follow these steps...

  1. You can create a Reference of the existing query.
  2. Then insert an Index column starting from 1 in the original query.
  3. Insert an Index column starting from 0 in the Referenced query.
  4. Then merge both the queries on Index Columns and extract the Start Time Field from the reference query.
  5. Now in the merged query you will have start time for one operation and start time of the next operation in the same row and you may then add a new column subtracting both the times.

 

Highlighted
Dear Subodh
Thank you so much! I will test this solution and will update you shortly.