I am requesting help calculating the difference between datetime values stored in sequential rows. I can do it in Excel but having trouble in PQ. Excel 2016 on PC.
Daily I pull in ~45,000 rows of repair order status data via SQL into a text file. The file is simply three (3) columns with a job order number (Order), job status (Status), and the datetime value when the job status changes (Status_Datetime). I pull the text file data into Excel via PQ and do a little sorting and cleanup. (My SQL server always generates a bunch of blank rows at the end for some reason.) The data comes into an Excel table as follows:
Order Status Status_Datetime
1CV7LQE A1 1/27/2021 13:08:00
1CV7LQE IW 6/7/2021 06:00:00
1CV7LQE M3 6/7/2021 11:30:00
1CV7LQE IW 6/7/2021 12:00:00
1CV7LQE M3 6/7/2021 16:00:00
1CV7LQE IW 6/8/2021 06:00:00
.... and so on for about 45,000 rows. There are thousands of distinct Order numbers and about 20 possible Status codes. Not all of the Status codes are used for each Order, but a value of "JC" indicates "Job Complete" and there should be no more records for that Ordeer number.
In Excel I can calculate the duration (days) spent in each job status easily enough:
Order Status_Datetime Status Duration
1CV7LQE 1/27/2021 13:08 A1 130.7027778
1CV7LQE 6/7/2021 6:00 IW 0.229166667
1CV7LQE 6/7/2021 11:30 M3 0.020833333
1CV7LQE 6/7/2021 12:00 IW 0.166666667
1CV7LQE 6/7/2021 16:00 M3 0.583333333
1CV7LQE 6/8/2021 6:00 IW 0.229166667
From here it's a simple matter to create a Pivot Table listing distinct Orders in rows and the total duration (days) for each job status in columns.
I would like to learn how to calculate the Duration values in a calculated column over in PQ. I've tried & tried but cannot crack the code (M-code) on how to accomplish this - especially when the Order number changes. I've attached two (2) files with sample data (greatly truncated, of course). Note: The PQ script will look for the files in a Desktop folder called "Job Status".
Any help would be greatly appreciated! And double bonus points if I can learn how to recreate the Pivot Table "report" directly in PQ. (That's a stretch, but I would like to learn as much as I can.)
Thank you very much for any assistance.
/Sam/ Oak Harbor, WA, USA
I received an error when uploading the files with my original post. Trying again...
I added a query that does what you ask for. Ikept it very basic and didn't do anything to make it any pretty.
See if you can follow the steps and come back here if you get stuck. Just point the source step to the txt on your own system.
Yes - this is perfect! And I learned (am starting to learn) how to do things better. By following other posts and YouTube videos I have been able to get as far as creating the two index columns, merging, and bringing the next row's data up to the current row in PQ. But after that I was lost and could not get the calculations to work when the primary grouping changed. My three (3) lessons from your reply and code:
Lesson #1: You used the ?? (coalesce) operator - which I have never previously seen or used. I had to look the ?? operator in the Microsoft M-code documentation. The specific line in your code is:
= Table.AddColumn(#"Expanded Expanded Custom.1", "Custom", each [Status_Datetime.1] ?? Date.StartOfDay (DateTime.LocalNow()))
Microsoft's official definition is: "The coalesce operator ?? returns the result of its left operand if it is not null, otherwise it will return the result of its right operand. The right operand is evaluated if and only if the left operand is null."
Riny, would you please help me better understand this by explaining - in plain language - how your code works with my sample data?
Lesson #2: You converted a datetime value to a number to perform a simple calculation) and later, in a subsequent step, you changed it back to its original datetime value for display purposes. Simple but genius. I cannot tell you how many times I made a simple task so difficult by creating a complicated formula to do the date math all in one step. Splitting it into two distinct steps, as you did, is so obvious and easy.
Lesson #3: After the PQ table is complete, you loaded it to a Pivot Table Report. I have not used this feature often, and usually bring all the data back into Excel and *then* create the Pivot Table. It's an old habit and I must practice and learn to use newer/better ways of accomplishing this task.
This was an excellent response and solution. In my opinion, this would be a great topic and example for a Mynda/Phil YouTube video. I am sure many people wrestle with the problem of comparing date values between successive rows in PQ.
Thank you, Riny! Looking forward to your explanation of the ?? (coalesce) function.
Replying to my own reply. Ugh. Riny van Eekelen's solution above worked perfectly - in Excel 365. When I tried to run it on my work PC, it didn't work. I'm stuck with Excel 2016 at work and apparently PQ does not support the ?? (coalesce) function or the Load to Pivot Table Report option in Excel 2016.
I have been reading up on the coalesce function and understand it better now, so no need to explain it to me. But now I have to figure out how to emulate it in Excel 2016. Looks like it mighty be replaced with a couple of "IF" statements but not sure.
Good to see that you were able to figure out most things yourself. That's the best way to learn!
With regard to replacing the coalesce function you can rewrite a formula in the Add Custom Column window like this:
if [Status_Datetime.1] = null then Date.StartOfDay ( DateTime.LocalNow() ) else [Status_Datetime.1]
