Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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
Creating a Monthly Date Table in Power Query M

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 make use of the List.Generate function in Power Query. If you’re new to the function, make sure you know 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.

Put together, the code for this looks like:

= 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
Date Rang ewith Monthly Steps in Power Query

Transform List to Table

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

Convert List To Table in Power Query

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.

Missing Column Name and Data Type

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 )
Set Column Name and Data Type

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.

Share on:

Latest from my blog

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.