• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Calculate datetime difference (duration) for values in different rows|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Calculate datetime difference (duration) for values in different rows|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCalculate datetime difference (dura…
sp_PrintTopic sp_TopicIcon
Calculate datetime difference (duration) for values in different rows
Avatar
Sam Calabrese
Whidbey Island, WA, USA

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 5, 2022
sp_UserOfflineSmall Offline
1
May 21, 2023 - 4:59 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Sam Calabrese
Whidbey Island, WA, USA

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 5, 2022
sp_UserOfflineSmall Offline
2
May 21, 2023 - 5:09 pm
sp_Permalink sp_Print

I received an error when uploading the files with my original post. Trying again...

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
3
May 21, 2023 - 6:45 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Sam Calabrese
Whidbey Island, WA, USA

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 5, 2022
sp_UserOfflineSmall Offline
4
May 22, 2023 - 2:33 am
sp_Permalink sp_Print

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.

Avatar
Sam Calabrese
Whidbey Island, WA, USA

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
March 5, 2022
sp_UserOfflineSmall Offline
5
May 22, 2023 - 1:36 pm
sp_Permalink sp_Print

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.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 490
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
May 22, 2023 - 2:04 pm
sp_Permalink sp_Print

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]

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Rashid Khan
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27793

 

Member Stats:
Guest Posters: 49
Members: 32324
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.