List.DateTimes

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.DateTimes

What is List.DateTimes?

List.DateTimes is a built-in M function in Power Query that generates a list of datetime values. It takes three arguments:

1. Start: The start datetime value

2. Count: The number of datetime values to generate

3. Interval: The time interval between each datetime value

For example, the following code generates a list of 10 datetime values starting from January 1, 2021, with an interval of 1 hour:

List.DateTimes(#datetime(2021, 1, 1, 0, 0, 0), 10, #duration(0, 1, 0, 0))

The output of this code will be a list of datetime values in the following format:

{

#datetime(2021, 1, 1, 0, 0, 0),

#datetime(2021, 1, 1, 1, 0, 0),

#datetime(2021, 1, 1, 2, 0, 0),

…

#datetime(2021, 1, 1, 9, 0, 0)

}

List.DateTimes is a very useful function for generating datetime ranges that can be used for various data analysis and reporting tasks.

The M Code Behind List.DateTimes

The M code behind List.DateTimes is fairly simple and can be broken down into three steps:

1. Calculate the duration between each datetime value

2. Generate a list of durations based on the count argument

3. Add each duration to the start datetime value to generate a list of datetime values

Here is the M code for List.DateTimes:

(start as datetime, count as number, interval as duration) =>

List.Generate(

() => {0, start},

each _{0} < count,

each { _{0} + 1, _{1} + interval },

each _{1}

)

Let's break down each line of this code:

1. The first line defines the function arguments: start, count, and interval.

2. The second line uses the List.Generate function to generate a list of datetime values.

3. The first argument of List.Generate is a function that returns the initial state of the list. In this case, the initial state is a tuple with two values: 0 (representing the index of the current datetime value) and the start datetime value.

4. The second argument of List.Generate is a function that defines the condition for continuing the list generation. In this case, the condition is that the index of the current datetime value is less than the count argument.

5. The third argument of List.Generate is a function that generates the next state of the list. In this case, the next state is a tuple with two values: the index of the next datetime value (which is incremented by 1) and the datetime value (which is incremented by the interval).

6. The fourth argument of List.Generate is a function that returns the current value of the list. In this case, the current value is the datetime value.

List.DateTimes is a powerful function in Power Query that generates datetime ranges based on the start, count, and interval arguments. The M code behind List.DateTimes is fairly simple and can be broken down into three steps: calculate the duration, generate a list of durations, and add each duration to the start datetime value. Understanding the M code behind List.DateTimes can help users write custom datetime functions and perform complex data manipulations 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)