8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.10 From Date & Time: Date, Time, Duration
| Menu | Options | Example |
|---|---|---|
| Date | Age, Date Only, Parse, Year (Year, Start/End of Year), Month (Month, Start/End of Month, Days in Month, Name of Month), Quarter (Quarter of Year, Start/End of Quarter), Week (Week of Year, Week of Month, Start/End of Week), Day (Day, Day of Week, Day of Year, Start/End of Day, Name of Day), Subtract Days, Combine Date and Time, Earliest, Latest | Order Date from Order DateTime; Name of Day for weekday analysis; Subtract Days between Order Date and Signup Date (select both) |
| Time | Time Only, Local Time, Parse, Hour (Hour, Start/End of Hour), Minute, Second, Subtract, Combine Date and Time, Earliest, Latest | Order Hour for the "orders by hour" chart (breakfast rush 7–9, evening 7–10 pm) |
| Duration | Days, Hours, Minutes, Seconds, Total Years, Total Days, Total Hours, Total Minutes, Total Seconds, Subtract, Multiply, Divide, Statistics | Delivered − Ordered → Total Minutes = delivery time (Module 7.14) |
Steps in Power BI – Order Date, Hour and Day Name in one go
- Select Order DateTime › Add Column › Date › Date Only → rename Order Date.
- Select Order DateTime › Add Column › Time › Hour › Hour → rename Order Hour.
- Select Order Date › Add Column › Date › Day › Name of Day → Day Name.
- Set the types: Date, Whole Number, Text.
Ravindra Bagale's Tip
He bagha, mitrano: adding Year, Month and Day columns to the Orders fact table is a common mistake. They belong in the Date table. The fact table only needs Order Date (and Order Hour if you analyse by hour), which keeps the fact table smaller and your time intelligence consistent. Practice kara, mag ekdum sope vatel.
Practice task
For the Customer table, add Signup Year and Customer Age (days) using Date › Age and Duration › Total Days. Explain why the age changes at every refresh.