Date.StartOfDay

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Date.StartOfDay

What is Date.StartOfDay?

Date.StartOfDay is a Power Query M function that is used to get the start of a day for a given date. This function returns a datetime value that represents the start of the day for the given datetime value. The time portion of the returned datetime value is set to 12:00:00 AM.

The M Code Behind Date.StartOfDay

The M code behind Date.StartOfDay is quite simple. The following is the M code for the Date.StartOfDay function:

(Date as datetime) as datetime => DateTime.Date(Date) & #time(0,0,0)

This code is a function that takes a datetime value as input and returns a datetime value. Let's break down this code step by step.

Step 1: DateTime.Date(Date)

The first part of the code, DateTime.Date(Date), is used to get the date portion of the datetime value. This function returns a date value that represents the date portion of the given datetime value. For example, if the input datetime value is "2022-01-01 10:30:00", the output date value will be "2022-01-01".

Step 2: & #time(0,0,0)

The second part of the code, "& #time(0,0,0)", is used to append the time portion to the date value. This function returns a time value that represents the time portion of the given datetime value. In this case, the time value is set to 12:00:00 AM, which is the start of the day.

Combining the Steps

When we combine these two steps, we get a datetime value that represents the start of the day for the given datetime value. For example, if the input datetime value is "2022-01-01 10:30:00", the output datetime value will be "2022-01-01 12:00:00 AM".

Examples of Date.StartOfDay Usage

Let's look at some examples of how Date.StartOfDay can be used in Power Query.

Example 1: Filter by Start of Day

Suppose we have a table that contains a datetime column, and we want to filter the rows that have a datetime value that falls on the start of the day. We can use the following M code to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQQA”, BinaryEncoding.Base64)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type datetime}}),

#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each [Column1] = Date.StartOfDay([Column1]))

in

#”Filtered Rows”

Here, we are using the Table.SelectRows function to filter the rows based on the condition [Column1] = Date.StartOfDay([Column1]). This condition checks if the datetime value in the column "Column1" is equal to the start of the day for that datetime value.

Example 2: Add Start of Day Column

Suppose we have a table that contains a datetime column, and we want to add a new column that contains the start of the day for each datetime value. We can use the following M code to achieve this:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQQA”, BinaryEncoding.Base64)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Column1”, type datetime}}),

#”Added Custom” = Table.AddColumn(#”Changed Type”, “StartOfDay”, each Date.StartOfDay([Column1]))

in

#”Added Custom”

Here, we are using the Table.AddColumn function to add a new column "StartOfDay" to the table. The value of this column is calculated using the Date.StartOfDay function with the datetime value in the "Column1" column as input.

In this article, we went through the M code behind the Power Query M function Date.StartOfDay. We saw that this function is used to get the start of a day for a given date. We also saw some examples of how this function can be used in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)