Time.StartOfHour

Time.StartOfHour is a Power Query M function that calculates the start of the hour for a given dateTime value. The function returns a time, datetime, or datetimezone value representing the start of the hour.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Time.StartOfHour( dateTime as any ) as any

Description

Time.StartOfHour returns the start of the hour represented by dateTime. dateTime must be a time, datetime, or datetimezone value. This function pinpoints the beginning of an hour from the specified time value, returning a new time value set to the start of that hour.

Examples

The Time.StartOfHour function can be useful in various scenarios, such as when you need to aggregate data by hourly intervals. Let’s explore how this function works through several examples.

Shifting a Time Value

Suppose you have the time value 14:17:00. To shift this to the start of the hour (14:00:00), you can use the following command:

// Output: #time( 14, 0, 0 )
Time.StartOfHour( #time( 14, 17, 0 ) )

This operation changes the minute and second components to zero, resulting in 14:00:00. In practical use, you will likely apply this function to a column of time values in your dataset.

When using this function, you will most likely apply it to a column with time values. Here’s what that could look like:

This code creates a new column with the time values shifted to the beginning of the hour.

Shifting a DateTime value

You can also use Time.StartOfHour on datetime values. For instance, to shift #datetime(2024, 12, 31, 23, 59, 59) to the start of the hour:

// Output: #datetime( 2024, 12, 31, 23, 0, 0)
Time.StartOfHour( #datetime( 2024, 12, 31, 23, 59, 59 ) )

This removes the minute and second portions, resulting in 2024-12-31 23:00:00. Applying this function to a column of datetime values would look like this:

This transformation sets the minute and second components of each datetime value to zero.

Shifting a DateTimeZone Value

Finally, Time.StartOfHour can also be applied to datetimezone values. For example, to shift #datetimezone(2024, 6, 29, 10, 35, 15, 2, 0) to the start of the hour:

// Output: #datetimezone( 2024, 6, 29, 10, 0, 0, 2, 0 )
Time.StartOfHour( #datetimezone( 2024, 6, 29, 10, 35, 15, 2, 0 ) )

This keeps the date and timezone offset the same but changes the time to 10:00:00.

When applying this to a column of datetimezone values you get the following results:

Even though the function name suggests the function works on time values, it is able to work with datetimezone values to. This results in each datetimezone value being adjusted to the start of the respective hour, with the timezone offset unchanged.

In short, the Time.StartOfHour function can be applied to time, datetime, and datetimezone values. It resets the minute and second components to zero, making it a handy tool for transforming time components in Power Query M.

Related functions

Other functions related to Time.StartOfHour are:

• Time.EndOfHour

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/time-startofhour