Time.Hour Function in Power Query M (4 Easy Examples)

Time.Hour returns the hour component, as a number from 0 to 23, of a time, datetime, or datetimezone value. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to pull just the hour out of a time or datetime value, so you can filter or group records by the time of day, Time.Hour is the function you reach for.

Table of Contents

Syntax of Time.Hour Function

Time.Hour(dateTime as any) as nullable number

where

dateTime (required, any). A time, datetime, or datetimezone value to read the hour from.

Returns: a number from 0 to 23 for the hour of the day. If dateTime is null, it returns null.

In plain terms, you hand it a time or datetime value and it gives you back the hour part on its own.

Example 1: Get the hour from a time value

Read the hour out of a plain time value.

let
    Result = Time.Hour(#time(14,5,0))
in
    Result

Result: 14

The value is 14:05:00, so the hour part is 14.

Example 2: Get the hour from a datetime value

Time.Hour reads the hour from a datetime too, ignoring the date part.

let
    Result = Time.Hour(#datetime(2024,3,18,9,42,30))
in
    Result

Result: 9

The date 2024-03-18 is ignored. The function only returns the hour, 9.

Example 3: Add an hour column to a table

Most of the time you will be pulling the hour out of a datetime column into a column of its own.

Say you have an Orders table with an OrderID and the datetime each order was Placed.

Here is the starting data:

OrderID	Placed
A-100	6/1/2024 8:15:00 AM
A-101	6/1/2024 1:47:00 PM
A-102	6/1/2024 7:03:00 PM

Add a column that applies Time.Hour to the Placed value of each row:

let
    Orders = #table(
        type table [OrderID = text,Placed = datetime],
        {
            {"A-100",#datetime(2024,6,1,8,15,0)},
            {"A-101",#datetime(2024,6,1,13,47,0)},
            {"A-102",#datetime(2024,6,1,19,3,0)}
        }
    ),
    AddHour = Table.AddColumn(Orders,"OrderHour",each Time.Hour([Placed]),Int64.Type),
    Result = Table.SelectColumns(AddHour,{"OrderID","OrderHour"})
in
    Result

This pulls each order’s hour into a new OrderHour column.

The result keeps the ID alongside the hour:

OrderID	OrderHour
A-100	8
A-101	13
A-102	19

The 1:47 PM order becomes 13 and the 7:03 PM order becomes 19, because the hour runs on the 24-hour clock. The Int64.Type tag types the column as a whole number, the same job Table.TransformColumnTypes does after the fact.

Example 4: Count rows in an hour range

You can use Time.Hour inside Table.SelectRows to keep only the rows that fall in a time-of-day window.

Say you have a Logins table and want to count the afternoon logins, meaning the hour is from 12 up to but not including 18.

Here is the starting data:

User	LoginAt
maya	6/1/2024 7:30:00 AM
raj	6/1/2024 12:05:00 PM
leo	6/1/2024 3:22:00 PM
ada	6/1/2024 9:40:00 PM

Filter to the afternoon hours, then count the rows that remain:

let
    Logins = #table(
        type table [User = text,LoginAt = datetime],
        {
            {"maya",#datetime(2024,6,1,7,30,0)},
            {"raj",#datetime(2024,6,1,12,5,0)},
            {"leo",#datetime(2024,6,1,15,22,0)},
            {"ada",#datetime(2024,6,1,21,40,0)}
        }
    ),
    Afternoon = Table.SelectRows(Logins,each Time.Hour([LoginAt]) >= 12 and Time.Hour([LoginAt]) < 18),
    Result = Table.RowCount(Afternoon)
in
    Result

Result: 2

Only raj at 12:05 PM and leo at 3:22 PM fall in the 12 to 18 window, so the count is 2.

Things to keep in mind with Time.Hour

It returns the hour only, never minutes or seconds. Time.Hour(#time(14,5,30)) is 14, with the 5 and 30 discarded. Use Time.Minute and Time.Second for those parts.
The hour is on a 24-hour clock. A 1:47 PM value returns 13, not 1. There is no AM/PM output.
null in gives null out. A blank or missing value returns null, not 0. Guard it with Time.Hour([Column] ?? #time(0,0,0)) if you need a number.
A date-only value throws. Passing a bare date raises Expression.Error: We cannot convert a value of type Date to type Time. Convert it to a datetime first, or it has no hour to read.
A text value will not work. "14:05" as text throws a type error. Convert it with Time.From or DateTime.From before calling Time.Hour.

Common questions about Time.Hour

How is Time.Hour different from DateTime.Time?

Time.Hour returns just the hour as a number, like 14. DateTime.Time returns the whole time portion as a time value, like 14:05:00, which you would still need to read the hour from. To grab the current clock value to feed in, use DateTime.LocalNow.

Can I group a table by hour with Time.Hour?

Yes. Add an hour column as in Example 3, then use that column as the grouping key in Table.Group to total or count rows per hour.

List of All Power Query Functions

Related Power Query Functions / Articles: