Date.StartOfDay - Date Function

Date.StartOfDay is a Power Query M function that returns the start of the day represented by dateTime. Your input must be a date, datetime, or datetimezone value.

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

Syntax

Date.StartOfDay( dateTime as any ) as any

Description

Date.StartOfDay identifies the start of the day for a given dateTime parameter (of type date, datetime, or datetimezone).

Examples

Shifting a DateTime Value

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

// Output: #datetime( 2024, 1, 1, 0, 0, 0 )
Date.StartOfDay( #datetime( 2024, 1, 1, 16, 0, 0 ) )

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

Date.StartOfDay shifts datetime values to beginning of day 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 start of the day:

// Output: #datetimezone( 2024, 6, 29, 0, 0, 0, 1, 0 )
Date.StartOfDay( #datetimezone( 2024, 6, 29, 12, 0, 0, 1, 0 ) )

Shifting a Date Value

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

Date.StartOfDay( #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.StartOfDay are:

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