How to Create One Row Per Month Between Two Dates in Power Query
Question details
The user wants to expand a dataset containing a start date and an end date for each person into multiple rows, generating one row for every month that falls within the specified date range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming summarized date range data into granular monthly records for detailed time-series analysis or reporting.
- Observed behavior
- The current dataset only provides a single row per person with a Start Date and End Date, rather than the desired output of individual rows representing each month in that period.
Ensure your source data is formatted as an official Excel Table (press Ctrl+T) and take note of the exact Table Name and column headers, as Power Query M code is case-sensitive.
Use Advanced Editor M Code to Expand Date Ranges
This solution uses a custom M code script in the Power Query Advanced Editor to dynamically calculate the months between your start and end dates and expand them into new rows.
By generating a list of dates between the Start and End dates and transforming them to the start of each corresponding month, you can extract a distinct list of months.
The List.ExpandListColumn function is then used to unpivot this list into individual rows per person.
Select any cell inside your data table. Navigate to the 'Data' tab on the Excel ribbon and click 'From Table/Range' to open the Power Query Editor.
In the Power Query Editor, go to the 'Home' tab and click on 'Advanced Editor' in the Query group. This will open the window displaying the background M code for your query.
Replace the existing code with the following snippet. Ensure you replace 'Tabelle1' with your actual table name, and match the 'Name', 'Start', and 'End' column headers exactly: let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], ChangedType = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start", type date}, {"End", type date}}), AddedPeriod = Table.AddColumn(ChangedType, "Period", each List.Distinct(List.Transform({Number.From([Start])..Number.From([End])}, each Date.StartOfMonth(Date.From(_))))), ExpandedPeriod = Table.ExpandListColumn(AddedPeriod, "Period"), RemovedColumns = Table.RemoveColumns(ExpandedPeriod,{"Start", "End"}) in RemovedColumns
Click 'Done' to apply the code. Select the new 'Period' column, change its Data Type to 'Date', and then click 'Close & Load' to return the expanded data to your Excel workbook. You can format the column as MMM-yyyy in standard Excel.
Looking for a Free, Powerful Alternative to Excel?
While advanced Power Query M-code is specific to Microsoft Excel, WPS Office provides a fully featured, lightweight, and completely free alternative for your daily spreadsheet, data analysis, and formatting needs.
- 1. Download WPS Office: Visit the official WPS Office website and click the download button for your operating system.
- 2. Install and Launch: Run the lightweight installer. Once finished, open WPS Spreadsheets.
- 3. Open Your Excel File: Drag and drop your existing .xlsx data files directly into the WPS interface to continue your data analysis seamlessly.
Frequently Asked Questions
How do I change the table name in the Power Query M code?
In the Advanced Editor window, locate the line that says `Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content]`. Replace `Tabelle1` with the exact name of your Excel table, keeping the quotation marks intact.
Can I format the resulting Period column directly in Power Query?
Power Query primarily handles data types rather than visual formatting. It is recommended to convert the Period column to a 'Date' type in Power Query, load it to Excel, and then apply a custom number format (like `MMM-yyyy`) to those cells in the Excel worksheet.
What happens if a person's Start and End dates are in the same month?
The provided M code uses `List.Distinct` and `Date.StartOfMonth`. If the Start and End dates fall within the same calendar month, the code will successfully return exactly one row for that month without duplicating data.
How do I fix the 'Column not found' error in Power Query?
This error occurs when the column names in your actual dataset do not match the column names referenced in the M code. Check the `Table.TransformColumnTypes` step in the code and update 'Name', 'Start', and 'End' to match your actual column headers exactly, including casing.
