Convert Start & End Dates into Monthly Periods in Power Query

Often, we come across datasets where each row represents a date range a Start date and an End date. But what if we want to break these ranges into individual months, like Jan-2025, Feb-2025, etc.? Whether you’re preparing reports, planning usage analysis, or aligning with monthly KPIs this transformation becomes super useful.

In this blog post, I’ll walk you through a Power Query M script that takes these Start-End dates and transforms them into distinct monthly periods for each record.

Understand the sample data & output

Our goal is to create a new list that shows every month-year combination that falls within those date ranges for each name and the output should look like this.

Step-by-Step how query works

1. Convert data into table -> Select Range -> Insert -> Table
2. Give the Table Name in our example we used it as “Table1”
3. Load the Table in Power Query
   • Only for Mac currently you need select blank query and then write in source “Excel.CurrentWorkbook()”
   • Windows right click and load data Table/Range
4. Change Types : Convert Start and End columns to Date format
5. Round to Full Months
   • Change Start to the first day of the month.
   • Change End to the last day of the month.
6. Generate a Date List
   • Convert dates to numbers to create a list using {[Start]..[End]} by adding custom column.
   • Expand this list into rows.
7. Extract Period Info
   • Get the Year Select Transform > Date > Year > Year
   • Get The Month Select Transform > Date > Month > Name Of the Month.
   • Extract only 3 character only from Name Of the Month.
   • Merge Name Of the Month + Year to convert it into MMM-YYYY format like Jan-2025.
8. Remove the column that are not required.
9. Remove the duplicate by selecting the name and period column
10. Your Data is ready

This format is perfect for monthly reports, aggregations, dashboards, or preparing a calendar-based dataset for Power BI.