List.Dates - List Function

List.Dates is a Power Query M function that generates a list of date values with a specified size, starting point, and step increment. The function returns a list of date values incremented by the given duration value.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Dates(
   start as date,
   count as number,
   step as duration,
) as list

Argument	Attribute	Description
start		This is the date from which the list begins.
count		This specifies the number of date values to generate in the list.
step		This is a duration value that determines the interval between each date in the list.

Description

The List.Dates function generates a list of dates. To do that it requires a start date and a count value that specifies the number values to generate. The step argument is a duration value that specifies the size of each step in the list of dates. For instance, if you specify a duration of 5 days, each step will increment by 5 days. In most situations, you will use a step of a single day.

Examples

Let’s understand the functionality of the List.Dates function with some practical examples.

Generating Four Consecutive Days

The first example generates a sequence of 4 consecutive dates. When specifying the arguments of List.Dates, you will commonly find the #date function used to specify a date value, and the #duration function for specifying a duration of each step.

Here’s how:

List.Dates( #date( 2024, 1, 1 ), 4, #duration( 1, 0, 0, 0 ) )

 /* Output: 
{   #date( 2024, 1, 1 ), #date( 2024, 1, 2 ), 
    #date( 2024, 1, 3 ), #date( 2024, 1, 4 )  }
 */

In this example,

Start Date: The first argument, #date(2024, 1, 1), sets the start date as January 1, 2024.
Count: The second argument, 4, indicates that the function should generate a total of 4 dates.
Step: The third argument, #duration(1, 0, 0, 0), specifies the increment for each step in the sequence. Here, the duration is set to one day.

The output demonstrates that each date in the sequence increases by one day, beginning from January 1, 2024, and continuing daily until January 4, 2024.

Generating a Date Sequence between Two Dates

Another common scenario is when you want to create a list of dates between two dates. For instance, let’s say you want to create a date sequence from the start to the end of 2024. You can easily do that use List.Dates. Have a look at this example:

Generating a list of dates between two specific dates is a frequent requirement. You can achieve this easily using the List.Dates function. This approach is especially useful when you need a complete sequence from a defined start date to an end date.

Let’s take a practical example where you want to generate a date sequence for the entire year of 2024, starting from January 1, 2024, and ending on December 31, 2024. Here’s how you can set up the function:

List.Dates( 
  #date( 2024, 1, 1 ), 
  Duration.Days( #date( 2024, 12, 31 ) - #date( 2024, 1, 1 ) ) + 1, 
  #duration( 1, 0, 0, 0 ) 
)

Start Date: The function begins with #date(2024, 1, 1), indicating the start of the date sequence.
Count: The count is calculated by subtracting the start date from the end date, #date(2024, 12, 31) - #date(2024, 1, 1), then using Duration.Days to convert this difference into the total number of days. Since both the start and end dates are inclusive, you need to add 1 to include both dates in the sequence, resulting in 366 days.
Step: The step is set to #duration(1, 0, 0, 0), which means the list will increment by one day at a time.

This code produces a date sequence covering every single day of 2024, from January 1 to December 31, inclusive. It’s a straightforward and effective way to handle date ranges for creating a calendar table.

Using Durations Smaller than a Day

The List.Dates function also allows for specifying increments smaller than a day, such as hours, minutes, or seconds. It’s important to note, however, that this function returns a list of date values, not datetime values. This means that any increment less than a full day will result in the same date unless the cumulative increase crosses into a new day.

For example, consider the following function call:

List.Dates(  #date( 2024, 1, 1 ), 6, #duration(0, 12, 0, 0 ) )

/* Output: 
{   #date( 2024, 1, 1 ), 
    #date( 2024, 1, 1 ), 
    #date( 2024, 1, 2 ), 
    #date( 2024, 1, 2 ), 
    #date( 2024, 1, 3 ), 
    #date( 2024, 1, 3 )  }
 */

This function starts generating dates from January 1, 2024, creating a total of six dates, with each subsequent date incremented by a duration of 12 hours.

Incrementing by 12 Hours: The duration of 12 hours is specified for each step. This function generates six dates starting from January 1, 2024.
Repeated Dates: The first two increments result in the same date, January 1, 2024, because adding 12 hours does not span into the next day. The next increments do cross into January 2 and January 3, respectively, each appearing twice in the list.

This shows List.Dates function handles increments less than a day by repeating the same date for each step increase that does not complete a full day.

Learn more about List.Dates in the following articles:

Creating Sequences in Power Query
Create Sequences in Power Query and Simplify your Code. Learn how to generate series of numbers, text, symbols, and dates with simple functions. » Read more
Create Date Table or Calendar in Power Query M
Learn how to create a dynamic calendar table in Power Query’s M language. Build your custom columns and claim your free Date Table Script. » Read more
Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Dates are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-dates