Excel Power Query: Create One Row for Each Task Date in an Interval
Question details
The user needs to expand each task record into one row for every calendar date between its start date and end date.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A source table contains task names with start and end dates, and the output must support one record per task-date combination.
- Observed behavior
- The desired output is a refreshed table containing every task date, including both boundary dates in each interval.
Confirm that the source data is formatted as an Excel table and that the start and end columns contain valid dates. Check that every end date is on or after its corresponding start date.
Expand task intervals into daily rows with Power Query
Use this refreshable approach when the source table changes regularly or contains many task intervals.
Power Query can create a list of dates for each task and then expand that list into separate rows. The count must be calculated as End Date minus Start Date plus 1 so that both boundary dates are included.
Select any cell in the source table, open the Data tab, choose From Table/Range, and confirm that the table has headers in the Power Query Editor.
Select the Start Date and End Date columns, open Transform > Data Type, and set both columns to Date.
Open Add Column > Custom Column, name the new column Task Dates, and enter: List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1, 0, 0, 0))
Select the Task Dates column, click the expand button in its header, and choose Expand to New Rows. Each task now appears once for every date in its interval.
Choose Home > Close & Load to place the expanded data in a worksheet. When the source table changes, use Data > Refresh All to regenerate the task-date rows.
Generate task-date rows with dynamic array formulas
Use this option in Microsoft 365 or Excel for the web when you need a formula-driven result without opening Power Query.
Manage Task-Date Spreadsheet Data with WPS Office
WPS Office is a free, lightweight Microsoft Office alternative for opening, reviewing, editing, and sharing task-date workbooks. It supports familiar spreadsheet workflows and Microsoft Excel XLSX files, making it easy to migrate source or expanded tables when Power Query is not required.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheet from the application launcher.
- 2. Open the workbook: Choose Open from the File menu and select the source or exported task-date workbook in XLSX format.
- 3. Review the expanded table: Use the filter controls and worksheet tools to inspect one row per task-date record, then save or share the workbook from File > Save As.
Frequently Asked Questions
How do I include both the start date and end date in Power Query?
In the Custom Column formula, calculate the list length with Duration.Days([End Date] - [Start Date]) + 1. The plus one ensures the end date is generated.
Can Power Query refresh the expanded task-date table automatically?
Yes. After loading the query with Home > Close & Load, update the source table and choose Data > Refresh All. Power Query will rebuild the date rows from the current intervals.
What happens if a task ends before it starts?
The date-list calculation becomes invalid or produces an error. Filter or correct those records before creating the Custom Column, and verify that End Date is greater than or equal to Start Date.
Can I create one row per task date without Power Query?
In Microsoft 365 or Excel for the web, use a dynamic array formula with SEQUENCE, REDUCE, and VSTACK. The formula spills the task and date combinations into the worksheet.
Why is my final task date missing from the output?
The list length was likely calculated as End Date minus Start Date instead of adding 1. Update the Power Query expression to Duration.Days([End Date] - [Start Date]) + 1.
