DateTimeZone.ToRecord

DateTimeZone

Returns a record with Year, Month, Day, Hour, Minute, Second, ZoneHours, and ZoneMinutes fields from a datetimezone value.

Syntax

DateTimeZone.ToRecord(dateTimeZone as datetimezone) as record

Parameters

Name	Type	Required	Description
`dateTimeZone`	`datetimezone`	Yes	A datetimezone value to decompose into its components.

Return Value

record — A record containing the Year, Month, Day, Hour, Minute, Second, ZoneHours, and ZoneMinutes components of the datetimezone.

Remarks

DateTimeZone.ToRecord decomposes a datetimezone value into a record with eight numeric fields: Year, Month, Day, Hour, Minute, Second, ZoneHours, and ZoneMinutes. The ZoneHours and ZoneMinutes fields reflect the UTC offset — for example, a value with offset +05:30 returns ZoneHours=5 and ZoneMinutes=30. For a -05:00 offset, ZoneHours=-5 and ZoneMinutes=0.

This function is most useful when you need to expand multiple components into separate table columns at once using Table.ExpandRecordColumn, instead of calling DateTimeZone.Year, DateTimeZone.Month, DateTimeZone.Day, DateTimeZone.Hour, DateTimeZone.Minute, DateTimeZone.Second, DateTimeZone.ZoneHours, and DateTimeZone.ZoneMinutes separately in eight Table.AddColumn calls.

For selective access to just the offset components, use DateTimeZone.ZoneHours and DateTimeZone.ZoneMinutes directly rather than decomposing the entire record. For datetime decomposition (without zone fields), use DateTime.ToRecord. For date-only decomposition, use Date.ToRecord.

Examples

Example 1: Expand datetimezone components into columns

Table.ExpandRecordColumn(
    Table.AddColumn(
        #table(
            type table [DTZ = datetimezone],
            {
                {#datetimezone(2024, 3, 15, 8, 30, 0, -5, 0)},
                {#datetimezone(2024, 6, 1, 14, 0, 0, 5, 30)}
            }
        ),
        "Parts", each DateTimeZone.ToRecord([DTZ])
    ),
    "Parts",
    {"Year", "Month", "Day", "Hour", "Minute", "Second", "ZoneHours", "ZoneMinutes"}
)

Result

	DTZ	Year	Month	Day	Hour	Minute	Second	ZoneHours	ZoneMinutes
1	3/15/2024 1:30:00 PM	2,024	3	15	8	30	0	-5	0
2	6/1/2024 8:30:00 AM	2,024	6	1	14	0	0	5	30

Example 2: Decompose a specific datetimezone value

DateTimeZone.ToRecord(#datetimezone(2024, 3, 15, 14, 30, 0, 5, 30))

Result

	Field	Value
1	Year	2,024
2	Month	3
3	Day	15
4	Hour	14
5	Minute	30
6	Second	0
7	ZoneHours	5
8	ZoneMinutes	30

Example 3: Use record fields inline to build an offset label

let
    DTZ = #datetimezone(2024, 3, 15, 9, 0, 0, -5, 0),
    R = DateTimeZone.ToRecord(DTZ),
    OffsetLabel = (if R[ZoneHours] >= 0 then "+" else "") &
                  Text.From(R[ZoneHours]) & ":" &
                  Text.PadStart(Text.From(R[ZoneMinutes]), 2, "0")
in
    #table(
        type table [DTZ = datetimezone, OffsetLabel = text],
        {{DTZ, OffsetLabel}}
    )

Applied Steps

The final output — a single-row table showing the original datetimezone alongside the formatted offset label string.

	DTZ	OffsetLabel
1	3/15/2024 2:00:00 PM	-5:00

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks