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
Look, friends: 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. Practise, and it will feel very easy.
Ravindra Bagale's Tip – मराठी
हे बघा मित्रांनो: Orders fact table मध्ये Year, Month आणि Day columns जोडणं ही common चूक आहे. त्यांची जागा Date table मध्ये आहे. Fact table ला फक्त Order Date लागते (आणि तासानुसार analysis करत असाल तर Order Hour), त्यामुळे fact table लहान राहतो आणि time intelligence सुसंगत राहते. Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
देखो दोस्तों: Orders fact table में Year, Month और Day columns जोड़ना common गलती है. इनकी जगह Date table में है. Fact table को सिर्फ़ Order Date चाहिए (और घंटे के हिसाब से analysis करो तो Order Hour), इससे fact table छोटी रहती है और time intelligence एक जैसी रहती है. Practice करो, फिर बहुत आसान लगेगा.
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.