7. Data Cleaning A–Z in Power Query
7.14 Dates and Times: Parts, Split DateTime, UTC to IST, Age and Duration
Extract parts with Add Column › Date / Time (or Transform tab to replace):
| From Order DateTime 2025-10-20 21:47 | Menu path | Result |
|---|---|---|
| Date only | Add Column › Date › Date Only | 20-10-2025 |
| Time only | Add Column › Time › Time Only | 21:47:00 |
| Year | Date › Year › Year | 2025 |
| Month name | Date › Month › Name of Month | October |
| Quarter | Date › Quarter › Quarter of Year | 4 |
| Week of year | Date › Week › Week of Year | 43 |
| Day name | Date › Day › Name of Day | Monday |
| Hour | Time › Hour › Hour | 21 |
| Start of month | Date › Month › Start of Month | 01-10-2025 |
Split a DateTime. Select Order DateTime › Add Column › Date › Date Only, then Add Column › Time › Hour › Hour. Relate Order Date to the Date table and use Order Hour for "orders by hour of day" charts (Module 14).
UTC to IST
Before (UTC)
| Order ID | Order Time UTC |
|---|---|
| AMZ-50001 | 2025-10-20 18:40 |
| AMZ-50002 | 2025-10-20 19:10 |
After (IST = UTC + 5:30)
| Order ID | Order DateTime IST | Order Date |
|---|---|---|
| AMZ-50001 | 2025-10-21 00:10 | 21-10-2025 |
| AMZ-50002 | 2025-10-21 00:40 | 21-10-2025 |
Notice that both orders move to the next day in IST. If you ignore time zones, late-night Diwali orders are counted on the wrong date.
Steps in Power BI
- Make sure the UTC column is Date/Time.
- Add Column › Custom Column › name
Order DateTime IST› formula below. - Set its type to Date/Time, then Add Column › Date › Date Only to get Order Date in IST.
IST = Table.AddColumn(Source, "Order DateTime IST", each
DateTimeZone.RemoveZone(
DateTimeZone.SwitchZone(DateTime.AddZone([Order Time UTC], 0), 5, 30)),
type datetime)
// simple alternative (India has no daylight saving):
// each [Order Time UTC] + #duration(0, 5, 30, 0)
Age and duration
- Customer tenure/age: select Signup Date › Add Column › Date › Age. This gives a duration from that date until now. Then use Transform › Duration › Total Years (or Days).
- Delivery duration: Add Column › Custom Column
[Delivered DateTime] - [Order DateTime]returns a duration such as0.00:09:30. Then Add Column › Duration › Total Minutes gives 9.5.
Dur = Table.AddColumn(Source, "Delivery Duration", each [Delivered DateTime] - [Order DateTime], type duration),
Mins = Table.AddColumn(Dur, "Delivery Mins (calc)", each Duration.TotalMinutes([Delivery Duration]), type number)
Ravindra Bagale's Tip
Friends, don't make a mistake here: relating an Order DateTime column (with time) to the Date table is a very common mistake, because nothing matches except midnight. Always create a date-only column for relationships. Also remember that Age calculated from "now" changes on every refresh, so compare with a fixed date column when you need stable numbers. Practise, and it will feel very easy.
Ravindra Bagale's Tip – मराठी
मित्रांनो, इथे चूक करू नका: Order DateTime column (वेळेसकट) Date table शी relate करणं ही खूप common चूक आहे, कारण मध्यरात्री सोडून काहीच match होत नाही. Relationships साठी नेहमी फक्त date असलेला column बनवा. आणि लक्षात ठेवा की "now" पासून काढलेलं Age प्रत्येक refresh ला बदलतं, म्हणून स्थिर आकडे हवे असतील तर fixed date column शी compare करा. Practice करा, मग एकदम सोपं वाटेल.
Ravindra Bagale's Tip – हिंदी
दोस्तों, यहाँ गलती मत करना: Order DateTime column (समय के साथ) को Date table से relate करना बहुत common गलती है, क्योंकि आधी रात के अलावा कुछ match नहीं होता. Relationships के लिए हमेशा सिर्फ़ date वाला column बनाओ. और याद रखो कि "now" से निकाली गई Age हर refresh पर बदलती है, इसलिए स्थिर आँकड़े चाहिए तो fixed date column से compare करो. Practice करो, फिर बहुत आसान लगेगा.
Practice task
For Amazon Now Nagpur orders stored in UTC, create Order DateTime IST, Order Date, Order Hour and Day Name. Check that an order at 2025-08-26 19:00 UTC falls on 27-08-2025 in IST.