DateTime.IsInNextNHours

What is the DateTime.IsInNextNHours function?

The DateTime.IsInNextNHours function is a useful function in Power Query that allows users to filter data based on a specific timeframe. This function returns a Boolean value, which is true if the date and time fall within the specified timeframe.

The function takes two arguments: the first argument is a column containing date and time information, and the second argument is the number of hours for which you want to check. For example, if you want to check whether a date and time fall within the next 3 hours, you would use the following formula:

= DateTime.IsInNextNHours([DateTimeColumn], 3)

How does the function work?

The DateTime.IsInNextNHours function works by comparing the date and time in the specified column to the current date and time plus the specified number of hours. If the date and time in the column fall within the specified timeframe, the function returns true. Otherwise, it returns false.

Here’s the M code behind the DateTime.IsInNextNHours function:

(DateTimeColumn as any, Hours as number) =>

let

CurrentDateTime = DateTime.LocalNow(),

EndDateTime = CurrentDateTime + #duration(0, Hours, 0, 0),

Result = DateTimeColumn >= CurrentDateTime and DateTimeColumn <= EndDateTime

in

Result

As you can see, the function first gets the current date and time using the DateTime.LocalNow function. It then adds the specified number of hours to the current date and time using the #duration function, which returns a duration value.

Finally, the function compares the date and time in the specified column to the current date and time plus the specified duration value. If the date and time fall within the specified timeframe, the function returns true. Otherwise, it returns false.

Examples of using the DateTime.IsInNextNHours function

Let’s take a look at some examples of using the DateTime.IsInNextNHours function in Power Query.

Example 1: Filtering data based on the next 3 hours

Suppose you have a table containing a column called “DateTime” that contains date and time information. You want to filter the table to only include rows where the date and time fall within the next 3 hours. Here’s how you would do that:

1. Select the column containing the date and time information.

2. Click on the “Add Column” tab and select “Custom Column”.

3. In the formula bar, enter the following formula:

= DateTime.IsInNextNHours([DateTime], 3)

4. Click “OK” to create a new column.

5. Filter the new column to only include rows where the value is “true”.

Example 2: Calculating the percentage of rows in the next 2 hours

Suppose you have a table containing a column called “DateTime” that contains date and time information. You want to calculate the percentage of rows where the date and time fall within the next 2 hours. Here’s how you would do that:

1. Select the column containing the date and time information.

2. Click on the “Add Column” tab and select “Custom Column”.

3. In the formula bar, enter the following formula:

= DateTime.IsInNextNHours([DateTime], 2)

4. Click “OK” to create a new column.

5. Click on the “Visualizations” tab and select “Table”.

6. Select the new column you just created.

7. In the “Values” section of the “Visualizations” tab, select “Count Rows”.

8. In the “Values” section of the “Visualizations” tab, select “Percent of Grand Total”.

Conclusion

The DateTime.IsInNextNHours function is a powerful tool in Power Query that allows users to filter data based on a specific timeframe. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it effectively in your data transformations.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us