List.Dates
ListGenerates a list of date values starting from a given date, with a specified count and step duration.
Syntax
List.Dates(start as date, count as number, step as duration) as listParameters
| Name | Type | Required | Description |
|---|---|---|---|
start | date | Yes | The starting date value. |
count | number | Yes | The number of date values to generate. |
step | duration | Yes | The duration to add between each successive date. Use #duration(days, hours, minutes, seconds). |
Return Value
list — A list of date values of length count, beginning at start and incrementing by step.
Remarks
List.Dates is the standard way to generate a continuous or stepped sequence of date values in Power Query M. It is most commonly used to build date dimension tables, fill in missing dates in time-series data, or create calendar scaffolding for reports.
The step parameter is a duration value created with the #duration(days, hours, minutes, seconds) literal. For day-by-day sequences use #duration(1, 0, 0, 0), for weekly use #duration(7, 0, 0, 0). Avoid using List.Dates for monthly sequences — because calendar months have variable lengths, use List.Generate with Date.AddMonths instead to produce accurate monthly increments.
A count of 0 returns an empty list; negative counts cause an error. To generate a full calendar year, calculate the count with Duration.Days(#date(year+1,1,1) - #date(year,1,1)) to handle leap years automatically.
Examples
Example 1: Generate a week of daily dates
List.Dates(#date(2024, 1, 1), 7, #duration(1, 0, 0, 0))Result | |
|---|---|
| 1 | 1/1/2024 |
| 2 | 1/2/2024 |
| 3 | 1/3/2024 |
| 4 | 1/4/2024 |
| 5 | 1/5/2024 |
| 6 | 1/6/2024 |
| 7 | 1/7/2024 |
Example 2: Generate weekly dates for a quarter
List.Dates(#date(2024, 1, 1), 13, #duration(7, 0, 0, 0))Result | |
|---|---|
| 1 | 1/1/2024 |
| 2 | 1/8/2024 |
| 3 | 1/15/2024 |
| 4 | 1/22/2024 |
| 5 | 1/29/2024 |
| 6 | 2/5/2024 |
| 7 | 2/12/2024 |
| 8 | 2/19/2024 |
| 9 | 2/26/2024 |
| 10 | 3/4/2024 |
| 11 | 3/11/2024 |
| 12 | 3/18/2024 |
| 13 | 3/25/2024 |
Example 3: Build a date dimension table
let
DateList = List.Dates(#date(2024, 1, 1), 366, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"})
in
DateTableThe final output — converts the date list into a single-column table with each date in its own row.
Date | |
|---|---|
| 1 | 1/1/2024 |
| 2 | 1/2/2024 |
| 3 | ... |