DateTime.IsInPreviousNHours

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function DateTime.IsInPreviousNHours

To understand how this function works, it’s important to take a closer look at the M code behind it. In this article, we’ll break down the M code used by DateTime.IsInPreviousNHours and explore some examples of how it can be used in practice.

Understanding the M Code

The M code behind DateTime.IsInPreviousNHours is relatively straightforward. It uses the DateTime.LocalNow function to retrieve the current date and time, and then subtracts a specified number of hours from that value. This creates a new datetime value that can be used to filter data.

Here’s the M code used by DateTime.IsInPreviousNHours:

DateTime.IsInPreviousNHours = (datetime as any, hours as number) =>

datetime >= DateTime.LocalNow() – #duration(0, hours, 0, 0)

Let's break down what's happening in this code:

- "DateTime.IsInPreviousNHours" is the name of the function we're defining.

- "(datetime as any, hours as number)" is the list of arguments that the function takes. In this case, we're expecting a datetime value and a number of hours.

- "datetime >= DateTime.LocalNow() - #duration(0, hours, 0, 0)" is the logic of the function. We're checking whether the datetime value is greater than or equal to the current datetime minus the specified number of hours.

So, if we were to call DateTime.IsInPreviousNHours with a datetime value of "10/1/2021 3:00 PM" and a number of hours of "2", the function would return "true" if the datetime falls within the range of "10/1/2021 1:00 PM" to "10/1/2021 3:00 PM".

Examples of DateTime.IsInPreviousNHours in Practice

Now that we understand the M code behind DateTime.IsInPreviousNHours, let's explore some examples of how it can be used in practice.

Example 1: Filtering Data based on the Last 24 Hours

Suppose we have a table of customer orders that includes a "Timestamp" column indicating when each order was placed. We want to filter this table to show only orders that were placed within the last 24 hours.

To do this, we can use the following M code:

let

Source = Excel.CurrentWorkbook(){[Name=”Orders”]}[Content],

FilteredRows = Table.SelectRows(Source, each DateTime.IsInPreviousNHours([Timestamp], 24))

in

FilteredRows

Here's what's happening in this code:

- "Source" is a reference to the table of customer orders.

- "FilteredRows" is a new table that we're creating by selecting only the rows from the "Source" table where the "Timestamp" value falls within the last 24 hours.

Example 2: Finding the Average Sales per Hour

Suppose we have a table of sales data that includes a "Timestamp" column indicating when each sale was made, as well as a "SalesAmount" column indicating the amount of the sale. We want to find the average sales amount per hour for the last 7 days.

To do this, we can use the following M code:

let

Source = Excel.CurrentWorkbook(){[Name=”Sales”]}[Content],

FilteredRows = Table.SelectRows(Source, each DateTime.IsInPreviousNHours([Timestamp], 168)),

GroupedRows = Table.Group(FilteredRows, List.Transform({“Timestamp”}, each Date.ToText(_, “MM/dd/yyyy hh:00”)), {{“Average Sales Amount per Hour”, each List.Average([SalesAmount]), type number}})

in

GroupedRows

Here's what's happening in this code:

- "Source" is a reference to the table of sales data.

- "FilteredRows" is a new table that we're creating by selecting only the rows from the "Source" table where the "Timestamp" value falls within the last 7 days (which is equivalent to 168 hours).

- "GroupedRows" is a new table that we're creating by grouping the rows in "FilteredRows" by hour, and calculating the average sales amount for each hour.

In this article, we've explored the M code behind the Power Query M function DateTime.IsInPreviousNHours and demonstrated how it can be used in practice. By using this function, you can easily filter and analyze data based on whether a date and time falls within a certain number of hours from the current date and time.

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)