? Date, Time & Duration
✨ Quick Overview
Date and Time analysis is the backbone of business intelligence. Dashboards rely on time-based logic to track growth, aging, and efficiency.
? Key Concepts
- Date: Year, Month, Day.
- Time: Hour, Minute, Second.
- DateTime: Precise moment in time.
- Duration: The elapsed time between two points (e.g., 2 days, 4 hours).
? Navigation Path
Path: Power Query Editor → Transform Tab (Modify existing) OR Add Column Tab (Create new) → Date / Time Group.
? Sample Dataset
? View Orders Data
OrderID,OrderDate,DeliveryDate 1,2024-01-05,2024-01-07 2,2024-02-10,2024-02-12
? Operations Summary
- Extracting: Pull Year, Month Name, or Quarter with one click.
- Calculating: Subtracting two dates creates a Duration type.
- Conversion: Durations can be converted to "Total Days" or "Total Hours" for math operations.
? Use Cases
- Logistics: Calculating shipping time (Delivery Date - Order Date).
- HR: Tracking employee tenure or years of service.
- Retail: Comparing Current Year sales vs. Previous Year.
⚡ Power Query Transform Simulator
Pick a date to see how Transform > Date tools extract information instantly.
? Selected: None
? Year: -
? Month: -
?️ Day Name: -
✅ Tips & Best Practices
- Data Types First: Always ensure your column is set to the "Date" type before clicking Transform.
- Add Column vs. Transform: Use Add Column if you want to keep the original date and create a new Year column.
- ISO 8601: Try to keep dates in
YYYY-MM-DD format to avoid regional confusion (US vs. UK dates).
- Durations to Numbers: After calculating duration, always use Duration > Total Days if you plan to use it in a sum or average.
? Try It Yourself
- Import an Excel file with a Date column.
- Go to Add Column → Date → Year → Year.
- Go to Add Column → Date → Month → Name of Month.
- Select two dates, go to Add Column → Date → Subtract Days.
- Check the "Applied Steps" to see the
Duration.Days formula.