7. Data Cleaning A–Z in Power Query

7.14 Dates and Times: Parts, Split DateTime, UTC to IST, Age and Duration

By Ravindra Bagale · Last updated: 25 September 2026

Extract parts with Add Column › Date / Time (or Transform tab to replace):

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

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.

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 as 0.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)