Date.EndOfDay

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.EndOfDay Function

The Date.EndOfDay function is used to find the last moment of the day, which is 23:59:59.9999999. It is used to convert a given date and time to the end of the day. For instance, if you have a date and time of January 1, 2021, 5:30 PM, the Date.EndOfDay function will convert it to January 1, 2021, 11:59:59.9999999 PM.

The M Code Behind Date.EndOfDay Function

The M code behind the Date.EndOfDay function is fairly simple. It involves using the DateTime.LocalNow function to get the current date and time, and then using the DateTime.Date function to get the date part of the current date and time. Finally, the DateTime.From function is used to combine the date part with the maximum time value of the day, which is 23:59:59.9999999.

The M code for the Date.EndOfDay function is as follows:

(DateTime.From(

DateTime.Date(

DateTime.LocalNow()

)

)

+ #duration(0, 23, 59, 59, 9999999)

)

Let's break down the code into its individual parts and understand how it works.

DateTime.LocalNow

The DateTime.LocalNow function is used to get the current date and time in the local time zone. It returns a datetime value that represents the current date and time in the format "yyyy-MM-ddThh:mm:ss.fffffff".

DateTime.Date

The DateTime.Date function is used to extract the date part from the datetime value. It takes a datetime value as input and returns a date value that represents the date portion of the input datetime.

DateTime.From

The DateTime.From function is used to combine the date part with the maximum time value of the day, which is 23:59:59.9999999. It takes a date value as input and returns a datetime value that represents the maximum time value of the input date.

#duration

The #duration function is used to specify the time duration. In this case, it is used to specify the maximum time value of the day, which is 23:59:59.9999999. The arguments to the function are in the format (#days, #hours, #minutes, #seconds, #milliseconds).

Usage of Date.EndOfDay Function

The Date.EndOfDay function is useful in scenarios where you need to find the end of the day for a given date and time. For example, you can use the function to filter data for a specific day, or to group data by day. Here's an example of how to use the Date.EndOfDay function in Power Query:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMtS1MlWyUlNISlWyUcpOz0lNijIzFV1y9Y0MvKtMBQAcwA=”, BinaryEncoding.Base64)), let _t = ((type nullable 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.EndOfDay(DateTime.LocalNow()))

in

#”Filtered Rows”

In this example, we are creating a table from a list of dates, converting the data type of the date column to datetime, filtering the rows based on dates that are less than or equal to the end of the day, and returning the filtered rows.

The Date.EndOfDay function is a powerful tool in Power Query that is used to find the last moment of the day. The M code behind the function is simple yet effective, and understanding how it works can help you use it more effectively in your data analysis and transformation tasks.

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)