Time.EndOfHour

Time.EndOfHour is a Power Query M function that calculates the end of the hour for a given dateTime value, including fractional seconds. The function returns a time, datetime, or datetimezone value representing the end of the hour.

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

Syntax

Time.EndOfHour( dateTime as any ) as any

Description

Time.EndOfHour returns the end of the hour represented by dateTime, including fractional seconds. Time zone information is preserved.

Examples

The Time.EndOfHour 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 end of the hour (14:59:59.9999999), you can use the following command:

// Output: #time( 14, 59, 59.9999999 )
Time.EndOfHour( #time( 14, 17, 0 ) )

This operation changes the minute and second components to fifty-nine and includes a fraction up to 7 decimals, resulting in 14:59:59.9999999.

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 end of the hour. In the image, you can see two columns: the “EndOfHour” column, which has applied the Time.EndOfHour function to the time values, and another column named “Time as Text.”

The “EndOfHour” column displays the results of the Time.EndOfHour function, but the user interface displays these time values as whole seconds. To show the actual precision of the value, including fractions of a second, the “Time as Text” column uses the Time.ToText function. This shows that the function indeed shifts the time to the exact end of the hour.

Shifting a DateTime value

You can also use Time.EndOfHour on datetime values. For instance, to shift #datetime(2024, 12, 31, 23, 45, 15) to the end of the hour:

// Output: #datetime( 2024, 12, 31, 23, 59, 59.9999999)
Time.EndOfHour( #datetime( 2024, 12, 31, 23, 45, 15 ) )

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

This transformation moves the minute and second components of each datetime value to the latest possible value. Here the DateTime.ToText function displays the time value with more details.

Shifting a DateTimeZone Value

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

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

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

When applying the Time.EndOfHour function to a column of datetimezone values, you get the following results:

Although the function name suggests it works on time values, it can also handle datetimezone values. This means each datetimezone value is adjusted to the end of the respective hour, while the date and timezone components remain unchanged. In the above image, the DateTimeZone.ToText function helps to format the new datetimezone value so that it also shows the fractions of a second.

In summary, the Time.EndOfHour function can be applied to time, datetime, and datetimezone values. It moves the minute and second components to highest possible value it can represent, shifting the time to the end of the hour.

Related functions

Other functions related to Time.EndOfHour are:

• Time.StartOfHour

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