If you want to build a list of dates in Power Query, starting on a given day and stepping forward by a fixed amount, the List.Dates function is what you reach for. It is handy for building a date column from scratch, like the backbone of a calendar or date table.
In this article, I’ll show you how List.Dates works and walk through a few examples of using it.
Syntax of List.Dates Function
List.Dates(start as date, count as number, step as duration) as list
where
start(required, date). The first date in the list. Build it with#date(year,month,day).count(required, number). How many dates you want in the list. This is a count of items, not an end date. To work a count out from two dates, see how to calculate date difference in Power Query.step(required, duration). The fixed gap between one date and the next. Build it with#duration(days,hours,minutes,seconds), so#duration(1,0,0,0)steps one day at a time and#duration(7,0,0,0)steps a week at a time.
Returns: a list of date values, starting at start and containing count dates spaced step apart.
In plain terms, you give it a starting date, how many dates you want, and how far apart they should be, and it hands back that list of dates.
A quick note on how the dates display. The examples below show results in YYYY-MM-DD form so they are unambiguous. In Power Query, the same dates show using your regional date format, so 2024-03-01 may appear as 1/3/2024 or 3/1/2024 on your machine.
Example 1: Build a list of consecutive days
Start on March 1, 2024 and get seven days in a row, one day apart.
let
Source = List.Dates(#date(2024,3,1),7,#duration(1,0,0,0))
in
Source
Result: {2024-03-01, 2024-03-02, 2024-03-03, 2024-03-04, 2024-03-05, 2024-03-06, 2024-03-07}
The step of #duration(1,0,0,0) is one day, so you get seven back-to-back dates.
Example 2: Step by a week with a weekly duration
Now build a list of week-start dates. Start on April 7, 2025 and get eight dates, each a week apart.
let
Source = List.Dates(#date(2025,4,7),8,#duration(7,0,0,0))
in
Source
Result: {2025-04-07, 2025-04-14, 2025-04-21, 2025-04-28, 2025-05-05, 2025-05-12, 2025-05-19, 2025-05-26}
The seven-day step moves a week at a time and rolls from April straight into May without you doing anything extra.
Example 3: Step by a custom number of days
The step does not have to be one day or one week. Here you start on June 3, 2024 and get seven dates, two days apart.
let
Source = List.Dates(#date(2024,6,3),7,#duration(2,0,0,0))
in
Source
Result: {2024-06-03, 2024-06-05, 2024-06-07, 2024-06-09, 2024-06-11, 2024-06-13, 2024-06-15}
A step of #duration(2,0,0,0) skips every other day, handy when you need a fixed every-other-day or every-few-days rhythm.
Example 4: Generate the start of a date range
A common job is building the first stretch of a calendar. Start on January 1, 2024 and get the first ten days of the year.
let
Source = List.Dates(#date(2024,1,1),10,#duration(1,0,0,0))
in
Source
Result: {2024-01-01, 2024-01-02, 2024-01-03, 2024-01-04, 2024-01-05, 2024-01-06, 2024-01-07, 2024-01-08, 2024-01-09, 2024-01-10}
Bump count up to 366 and you have every date in the year, ready to turn into a date table. From there you can add columns like today’s date in Power Query for reference.
Things to keep in mind with List.Dates
stepis a fixed duration, not a calendar step. It adds the same number of days every time, so it is great for clean daily or weekly spacing but it cannot do “first of each month” or “same day next year”, since months and years vary in length.- For monthly or yearly dates, use a different approach. Reach for
List.GenerateorList.TransformwithDate.AddMonthsorDate.AddYears, which step by calendar months and years correctly. stepmust be aduration, not a number. Passing a plain number throwsExpression.Error: We cannot convert the value ... to type Duration.Wrap it in#duration(d,h,m,s).- The result is a
list, not a table. To build a date column for a calendar, convert it with#tableorTable.FromList, then set the column type todate.
Common questions about List.Dates
How do I make a list of the first day of each month?
List.Dates cannot do this because its step is a fixed number of days. Use List.Transform({0..11}, each Date.AddMonths(#date(2024,1,1), _)) to get the first of each month for a year.
How do I go up to an end date instead of a count?
Calculate the count from the two dates, then pass it in. For a daily list, count is Duration.Days(EndDate - StartDate) + 1, so a list from January 1 to January 10 needs a count of 10.
How do I turn the list into a table or calendar?
Wrap the list in Table.FromList (or #table) to get a single date column, then add columns like year, month, and weekday with Date.Year, Date.MonthName, and Date.DayOfWeekName to build a full date table.
List of All Power Query Functions
Related Power Query Functions / Articles:
