Time.Hour

D

Back to top

T

Back to top

#

Back to top

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

What is the Time.Hour function?

The Time.Hour function is used to extract the hour component from a given time value. The function takes a time value as input and returns the hour component as an integer. For example, if the input time value is 10:30:45 AM, the Time.Hour function will return 10.

Syntax of the Time.Hour function

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

Time.Hour(dateTime as any) as any

The function takes a single parameter, dateTime, which represents the input time value. The parameter can be of any data type that can be converted to a time value. The function returns the hour component of the input time value as an integer.

Understanding the M code behind the Time.Hour function

The M code behind the Time.Hour function is relatively straightforward. The function simply extracts the hour component from the input time value using the DateTime.LocalNow() function and the DateTime.TimeOfDay property. Here’s the M code for the Time.Hour function:

(Time as any) =>

let

Source = DateTime.LocalNow(),

TimeOfDay = Time - DateTime.Date(Source),

Hour = TimeOfDay / #duration(0, 1, 0, 0),

in

Hour

Let’s break down the M code line by line:

– The first line defines the function and its input parameter, Time.

– The second line creates a new variable, Source, which represents the current date and time.

– The third line calculates the time of day by subtracting the date component of the input time value from the current date and time.

– The fourth line calculates the hour component by dividing the time of day by a duration of one hour.

Using the Time.Hour function in real-world scenarios

The Time.Hour function can be used in a wide range of scenarios, such as:

– Calculating the average time of day for a set of data points.

– Aggregating data by hour for time-based data analysis.

– Extracting the hour component from a timestamp for merging with other data sets.

– Calculating the duration between two time values in hours.

Here’s an example of how the Time.Hour function can be used to extract the hour component from a set of timestamp data:

let

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

#"Converted to Date/Time" = Table.TransformColumns(Source,{{"timestamp", DateTime.FromText, type datetimezone}}),

#"Extracted Hour" = Table.AddColumn(#"Converted to Date/Time", "Hour", each Time.Hour([timestamp]))

in

#"Extracted Hour"

In this example, we start with a table of timestamp data and use the Time.Hour function to extract the hour component for each timestamp. The resulting table includes a new column, Hour, that contains the hour component for each timestamp.

The Time.Hour function is a useful tool for extracting the hour component from a given time value in Power Query. By understanding the M code behind the Time.Hour function, users can gain a deeper understanding of how Power Query works and how it can be used to transform and manipulate data. Whether you’re a data analyst, a business intelligence professional, or a data scientist, the Time.Hour function is one of many powerful tools available in Power Query that can help you achieve your data analysis goals.

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)