Time.EndOfHour

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Time.EndOfHour

Understanding Power Query M Function

Power Query M function is a language used for data transformation and analysis. It is used in Microsoft Power BI, Power Query, and Excel. The M language is designed to be user-friendly and easy to read, but it can also be complex and powerful.

In Power Query, M functions are used to transform data. The functions can be simple, such as adding columns or filtering data, or complex, such as grouping data and creating custom functions. The Time.EndOfHour function is one of the many built-in functions in Power Query M that can be used to manipulate time-based data.

Syntax of Time.EndOfHour

The syntax of Time.EndOfHour is simple and easy to understand. The function takes one argument, which is a datetime value. The function returns the last second of the current hour.

The syntax of the Time.EndOfHour function is as follows:

Time.EndOfHour(dateTime as any) as datetime

The argument dateTime is any datetime value.

M Code Behind Time.EndOfHour

To understand the M code behind the Time.EndOfHour function, let’s first understand the concept of datetime in Power Query. A datetime value in Power Query is a combination of a date and a time. The date and time are represented as numbers in Power Query.

The date is represented as the number of days after December 30, 1899, while the time is represented as the decimal part of a day. For example, noon is represented as 0.5 in Power Query.

The M code behind the Time.EndOfHour function is simple. It takes the datetime value as an argument and converts it to the end of the hour. Here is the M code behind the Time.EndOfHour function:

(dateTime as any) => DateTime.DateAdd(DateTime.LocalNow(), "hour", DateTime.Hour(dateTime)+1)-#duration(0,0,1,0)

Let’s break down the code.

– The argument dateTime is passed to the function.

– The DateTime.LocalNow() function returns the current date and time.

– The DateTime.Hour(dateTime) function returns the hour of the dateTime value.

– The DateTime.DateAdd(dateTime, “hour”, 1) function adds one hour to the dateTime value.

– The #duration(0,0,1,0) function returns a duration of one second.

– The -#duration(0,0,1,0) subtracts one second from the dateTime value to get the last second of the hour.

Examples of Time.EndOfHour

Let’s look at some examples of how to use the Time.EndOfHour function in Power Query.

Example 1

Suppose we have a table with a datetime column named “DateTime”. We want to add a new column that contains the last second of the hour for each datetime value.

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0jQzNjVQ0gAzKLCnUWKKKKJKkrIyNjQ1MgGhA6ZoYQBiWA==", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}}),

#"Added End of Hour" = Table.AddColumn(#"Changed Type", "End of Hour", each Time.EndOfHour([DateTime]))

in

#"Added End of Hour"

Example 2

Suppose we have a list of datetime values. We want to get the last second of the hour for each datetime value.

let

Source = {#datetime(2020, 12, 31, 23, 45, 0), #datetime(2021, 1, 1, 0, 1, 30), #datetime(2021, 1, 1, 1, 15, 0)},

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"DateTime"}),

#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"DateTime", type datetime}}),

#"Added End of Hour" = Table.AddColumn(#"Changed Type", "End of Hour", each Time.EndOfHour([DateTime]))

in

#"Added End of Hour"

The Time.EndOfHour function is a very useful function in Power Query M. It can be used to get the last second of the hour for any datetime value. Understanding the M code behind this function is important to fully utilize its capabilities. In this article, we have discussed the syntax and M code behind the Time.EndOfHour function and provided examples of how to use it 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)