DateTime.IsInPreviousNMinutes

D

Back to top

T

Back to top

#

Back to top

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

Overview of the DateTime.IsInPreviousNMinutes Function

The DateTime.IsInPreviousNMinutes function is used to filter data based on a specific time frame. It takes two parameters: the column that contains the date and time values, and the number of minutes to consider. The function returns a Boolean value of true or false, depending on whether the value in the column is within the specified time frame.

The function is useful when you want to extract data that falls within a certain time frame, such as the last hour or the last day. For example, you may want to extract all the sales data from the last hour to see how your business is performing in real-time.

The M Code Behind the Function

The M code behind the DateTime.IsInPreviousNMinutes function is quite simple. It uses the DateTime.LocalNow function to get the current date and time, subtracts the number of minutes specified by the second parameter, and compares the result to the date and time value in the specified column.

Here is the M code for the function:

(Table as table, ColumnName as text, Minutes as number) =>

let

Source = Table.SelectRows(Table, each [ColumnName] >= DateTime.LocalNow() – #duration(0,0,Minutes,0))

in

Source

The function takes three parameters: the table that contains the data, the column name that contains the date and time values, and the number of minutes to consider. It then uses the Table.SelectRows function to filter the data based on the specified time frame.

Using the Function in Power Query

To use the DateTime.IsInPreviousNMinutes function in Power Query, you need to create a new custom function. Here are the steps:

1. Open the Power Query Editor by clicking on the Edit Queries button in the Home tab of Excel.

2. Click on the New Source button and select Blank Query.

3. In the Query Editor, click on the View tab and select Advanced Editor.

4. Paste the M code for the function into the Advanced Editor window.

5. Replace the Table, ColumnName, and Minutes parameters with the appropriate values for your data.

6. Click on the Done button to save the function.

7. You can now use the function in other queries by calling it from the formula bar.

Here is an example of how to use the function to extract data from the last hour:

1. Create a new query that contains the data you want to extract.

2. Create a new custom column by clicking on the Add Column tab and selecting Custom Column.

3. Enter the following formula in the formula bar: DateTime.IsInPreviousNMinutes(Source, "DateColumn", 60)

4. Replace "Source" with the name of your query, "DateColumn" with the name of the column that contains the date and time values, and 60 with the number of minutes you want to consider.

5. Click on the OK button to create the custom column.

6. The custom column will contain Boolean values of true or false, depending on whether the date and time value falls within the specified time frame.

7. You can now filter the data based on the custom column to extract the data you need.

The DateTime.IsInPreviousNMinutes function is a powerful tool that allows you to extract data based on a specific time frame. By understanding the M code behind the function, you can create custom functions that suit your specific needs. Power Query is a versatile tool that can save you time and effort in extracting and transforming data, and the DateTime.IsInPreviousNMinutes function is just one example of its capabilities.

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)