Date Table with Monthly Increments in Power Query

Are you looking to create a date table with Monthly increments in Power Query? Well, I have some good news for you. With Power Query, creating a monthly calendar is easy.

You don’t actually have to include every single date like a regular calendar. Instead, you can just show a single value for each month. In this article, we’ll walk through the step-by-step process of creating a monthly calendar in Power Query.

Table of contents

• Why Create a Monthly Date Table?
• Create a Date Range with Monthly Increments
• Transform List to Table
• Create Additional Columns
• Conclusion

Why Create a Monthly Date Table?

So why would you want to create a calendar that has monthly increments instead of daily? There are different scenarios where this proves useful. To name a few:

1. Budgeting and forecasting: When your team provides you with a yearly budget, allocating it over the year can be useful. Using the logic in this article, you can take a year value, generate the 12 months of the year, and split a record into 12. You can use this result to allocate your budget over different months.
2. Snapshot: your data model may require a monthly table to save snapshot values. You can prepare your table in Power Query, summarize your values and connect it to the rest of your model.
3. Project Management: you can use a monthly calendar to track project progress and milestones. Some planning is required monthly, and this method helps prepare your template.

Besides these examples, there are plenty of scenarios where it’s useful to increment a date with months. So how can you achieve that?

Create a Date Range with Monthly Increments

The first step in creating a date table that increments by month is to create the date range. This date range increments with monthly steps instead of daily steps.

Functions like List.Dates are a bit tricky here because they require a duration of days. Instead, you can use the List.Generate function in Power Query. If you’re new to the function, make sure you understand List.Generate.

In this case, it works as follows.

1. As the first argument, you provide a starting date for your calendar. You can hardcode this value like below or make a more dynamic formula.
2. Next, you determine how long List.Generate needs to compute the function. In the example below, this is up till the end of 2024.
3. In the third argument, you tell the function what it needs to do with each step. To increment each step by a month, you can use the Date.AddMonths function.

To create a list of months between dates you can use:

List.Generate( 
  () =>     #date( 2022, 1 , 1 ), // Starting value
  each _ <= #date( 2024, 12, 31), // Create only when <= 31 dec 2024
  each Date.AddMonths( _, 1 ) // Take steps of 1 month
)   

Transform List to Table

Next, transform the list to a table by clicking To Table in the transform tab.

Power Query generates the below code using the Table.FromList function.

Table.FromList( 
  Source, 
  Splitter.SplitByNothing(), 
  null, 
  null, 
  ExtraValues.Error 
)

The result of that is a single-column table with an undefined Data Type and the Column name Column1.

You could add additional steps to rename the column and provide the column type. However, that adds 2 additional steps. Instead, I recommend adjusting the code slightly and achieving both actions in the same step. To provide your column with both a data type and column name, you can change the code to the following:

Table.FromList( 
  Source, 
  Splitter.SplitByNothing(), 
  type table[Date = Date.Type], 
  null, 
  ExtraValues.Error
)

Create Additional Columns

Great. You now have the date range for your date table. From here, you can add all the desired additional columns. For example:

Date.Year( [Date] )               // Returns the year number
Date.MonthName( [Date] )          // Returns month name
Date.Month( [Date] )              // Returns Month Number
Date.ToText( [Date], "MMM yyyy" ) // Returns Short Month and Year, e.g. Jan 2023

Conclusion

To conclude, by using the List.Generate function, you can create a date range that increments by months instead of days. And when you learn how to use the Table.FromList function, you can easily convert that date range into a table with the right column name and data type. From there, you can easily add additional columns to enrich your monthly date table.