Date.EndOfDay - Date Function

Date.EndOfDay is a Power Query M function that returns the end of the day, preserving time zone information. The function returns the end of the day as a date, datetime, or datetimezone value.

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

Syntax

Date.EndOfDay( dateTime as any ) as any

Description

Date.EndOfDay finds the end of the day for a given date, datetime, or datetimezone value (dateTime), maintaining time zone information.

Examples

Shifting a DateTime Value

The Date.EndOfDay function works most effectively with datetime or datetimezone values. For instance, to shift a datetime value of January 1st, 2024, 12:00 AM to the end of the day, you can use:

// Output: #datetime( 2024, 1, 1, 23, 59, 59.9999999 )
Date.EndOfDay( #datetime( 2024, 1, 1, 0, 0, 0 ) )

To visualize this, imagine a table with a datetime column containing 12 datetime values. You can shift each of these to the end of the day by using Date.EndOfDay:

Date.EndOfDay function to shift a datetime value in Power Query M

Shifting a DateTimeZone Value

Similarly, applying the logic to a datetimezone value results in the same date but with the time shifted to the end of the day:

// Output: #datetimezone( 2024, 6, 29, 23, 59, 59.9999999, 1, 0 )
Date.EndOfDay( #datetimezone( 2024, 6, 29, 0, 0, 0, 1, 0 ) )

Shifting a Date Value

You can also apply the Date.EndOfDay function to a date value. However, since there is no time component involved, it has no impact:

Date.EndOfDay( #date( 2024, 6, 29 ) ) // Output: #date( 2024, 6, 29 )

Therefore, applying this function on a date like June 29 2024 will return the same date value:

Other functions related to Date.EndOfDay are:

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