Create Calendar Table Using Power Query M Language

By:   |   Comments (11)   |   Related: > Power BI


There have been many techniques for creating a Calendar Date table in Power BI using mostly DAX. However, there might be reasons one might want to do the same using Power Query M language which might be for Model Performance reasons or convenience reasons. This post will demonstrate ways I have achieved this using a Custom Column and the M language in Power Query. There are other approaches to doing this as seen in this awesome YouTube video from Enterprise DNA. For those who want to understand the steps on how they can achieve this in a simplified approach that does not require the need to create a lot of query functions that might impact model performance, then continue reading this article.


To demonstrate this, I have separated the steps as follows:

  1. Pick your Start Date and enter it in a table column
  2. Create your End Date using M language function
  3. Add a column for date ranges between StartDate and EndDate
  4. Remove the StartDate and EndDate columns and add Other columns
  5. Load Calendar dates table to Power BI data model

STEP 1: Pick your Start Date and enter it in a table column

Depending on your business or task requirement, you can choose a date when to start your date range. For instance, if your order date started on January 31, 2010, you might want to enter the start date as "31/01/2010". However, in this post I have used a hardcoded Start Date of "01/01/2000" (with the assumption that at least I am sure that should be within the date range I am going to be working with in my task).

To do this, within a Power Query Editor window in Power BI click on "Enter Data", and when the dialog window opens enter your hardcoded date and name the column as you may wish, but I have named mine "StartDate". See below.

Creating a column with Start Date

STEP 2: Create your End Date using M language function

Since we should expect the end date to change overtime in any Calendar date table, we could either create the end date to have a far future date like dates into 2025 or we can create a dynamic enddate. The former is not so efficient as it can consume a lot of space in your model and in the long run might impact performance and is not hundred percent future proof.

To setup the Enddate in a dynamic way, we need to use todayís date as the end date (however, this depends on the business requirement). To do this, within the Power Query Editor we click on the "Add Column" tab and then select "Custom Column" as seen in the diagram below. The M Query used here starts with the "Date.From" function, you can read more about this function here. A second function "DateTime.LocalNow()" was used to return today’s date and you can read more about this function on this useful post.

Creating a column with End Date

Once the M code above is entered correctly you should now be able to see the two columns "StartDate" and "EndDate" as seen in the diagram below. Please remember to change the datatypes of both columns to Date datatypes.

Diagram showing where to change data types

STEP 3: Add a column for date ranges between StartDate and EndDate

Next, we need to add a column to the table that will include date ranges from the StartDate of "01/01/2000" and the EndDate of today.

To do this, once again we need to click on the "Add Column" tab in the Power Query Editor window and select "Custom Column". See the diagram below. Then, we need use the function "Number.From" which will help us convert a date into numbers so that we can get a list of the numbers and later convert the data type of the column into date.

Diagram showing where to create Custom Column 1

Once the code is entered accurately, you should now be able to see the table as seen in the diagram below. You would need to expand the "Dates" column.

Diagram showing where to expand a column value

Once the "Date" column has been expanded, you should then be able to see the date range lists, but in numbers, since we converted the dates to numbers using "Number.From" function earlier (remember, dates can be represented in numbers like in Excel). See the diagram below.

Diagram showing expanded column values

Then, we need to change the data type of the "Dates" column to Date data type so we can better understand the content of the column as seen in the diagram below.

Diagram showing where to change date data type

STEP 4: Remove the StartDate and EndDate columns and add Other columns

As you can see from the diagram above, we would not need the StartDate and EndDate columns as they have repetitive values and are not required in what we will be doing going forward. So, we remove both columns as seen in the diagram below.

Diagram showing how to remove columns

Next, we create a column for "Year" by going to "Add Column" and selecting "Custom Column" as before as seen in diagram below. This is done using the "Date.Year" M Query function. Get more understanding of this function here.

Diagram showing where to create Custom Column 2

Next, we create a "Month", "MonthName" and "ShortMonthName" columns as seen in the diagrams below respectively. For each column we need to use the M Query codes below within the Custom Column window.

Diagram showing where to create Custom Column 3
Diagram showing where to create Custom Column 4
Diagram showing where to create Custom Column 5

Let us add another column for "Quarter" of the year as seen in the diagram below.

Diagram showing where to create Custom Column 6

So, to add to the "Quarter" of the year column we might like to get values like "Qtr 1", "Qtr 2" etc. To get this we need to adjust the M code above a little bit as seen in the diagram below. Note, please remember to change the data type of the "Quarter" column to "Text" before this step to prevent errors.

Diagram showing where to create Custom Column 7

I want to stop there for the purpose of this blog post as I prefer tips that are not too long and complex to read through, I am sure most people are like that too. All we have done can be seen in the diagram below.

Diagram showing all created columns

STEP 5: Load Calendar dates table to Power BI data model

Before we load the table to the Power BI Model let us rename the table appropriately to "CalendarTable".

Diagram showing changed table name

Then we load the table to the model. By now we should be able to open the "Advanced Editor" window to see the full M code behind this Calendar table as seen below.

Diagram showing M Query within Advanced Editor window
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Year", each Date.Year([Dates])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Month", each Date.Month([Dates])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "MonthName", each Date.MonthName([Dates])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "ShortMonthName", each Text.Start([MonthName],3)),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Quarter", each Date.QuarterOfYear([Dates])),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom6",{{"Quarter", type text}}),
    #"Added Custom7" = Table.AddColumn(#"Changed Type3", "QtrText", each "Qtr "& [Quarter])
    #"Added Custom7"

You can then reuse the M code in another Power BI report where applicable, and you can add more columns to the table as the business requires, for example, you might want to add "Year-Month", or "WeekNumber", or "DayName", or "Day" columns. All these would then be reflected in the summary M code within the Advanced Editor.

Next Steps
  • Check out these additional resources:
  • See this YouTube video from Ruth of Curbal on creating list of dates in Power Query here.
  • You can get more detailed documentations on M Query Language from Microsoft here.
  • Check out these series of blog posts by Jaykilleen on Power Query here.
  • Also, checkout this helpful YouTube video on M Query training by Ruth of Curbal here.
  • Try this tip out in your own data as business requires.

Learn more about Power BI in this 3 hour training course.

Click here to start the Power BI course

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelorís and masterís degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, June 17, 2023 - 9:31:28 AM - Vikas Back To Top (91309)
Thanks a lot for such detailed instructions, it answered my all queries.

Tuesday, February 7, 2023 - 1:34:45 PM - Aigbe Kenneth Omorodion Back To Top (90891)
Hi Rick,

Good to hear from you. Thanks for the heads up, although i try to make my blogs as easy as possible to assimilate by any one, but your suggested approach is great too, i will take some time to read your blog and possibly update mine too.

Thanks once more, i appreciate.


Monday, February 6, 2023 - 4:34:53 PM - Rick de Groot Back To Top (90887)
Hey Kenneth,

Thanks for the article. I like how you build up this article with screenshots, making it easy to follow your steps.
I was wondering.
At the start you retrieve the number-equivalent of the start and end date, generate a list of dates, and return it to date type. Have you considered doing this all using List.Generate? You could do something like:

= List.Generate(
() => StartDate,
each _ <= EndDate,
each Date.AddDays(_, 1 )

It's a fun challenge the first time you see it, but once you get it it's such a versatile function. You can see some of that in action right here:

Ofcourse there's so many methods, also like List.Dates and List.Numbers combined with your suggestion.

But you probably have your reasons. Curious to know :)


Friday, April 1, 2022 - 4:31:08 PM - Jimi Back To Top (89960)
Thanks Kenneth. This is insightful.

Tuesday, July 27, 2021 - 1:22:23 PM - Arthur Back To Top (89056)
Does this table have the date of its creation?
Does it update "every day"?

Tuesday, July 27, 2021 - 8:17:49 AM - Nick Back To Top (89052)
Thank you. Very helpful article.

Wednesday, March 3, 2021 - 12:00:05 AM - Jeff Moden Back To Top (88324)
Thanks, for the reply, Kenneth.

Sunday, February 28, 2021 - 4:23:02 PM - Aigbe Kenneth Omorodion Back To Top (88315)
Hi Jeff,

Thanks for the appreciation, well received!

Yes you can use a date table from a Data Warehouse as a date table in Power BI data model. For instance, the DimDate table in a Data Warehouse is a pure date table. The reason modellers create another date table in Power BI data model is because there is no date table to use, so they you need create one in such instance. Date table are particularly very useful when you need to make some time intelligence calculations.

Hopefully this helps.


Saturday, February 27, 2021 - 6:33:48 PM - Jeff Moden Back To Top (88312)
Hi Kenneth,

Great article. Thanks for taking the time to do the research and put it together.

I do have a question, though. Can Power BI use an already existing Calendar Table that lives in a database?

Friday, February 26, 2021 - 4:12:28 PM - Aigbe Kenneth Omorodion Back To Top (88309)
Hi Shar, good to hear that this article is helpful to you also. I have not included Fiscal Year calculation in this article. But after you load it to Power BI model you could create a calculated column for fiscal year using the DAX code as follows:

FiscalYear =
VAR _FiscalYrStrt = 4 //Update as appropriate with 4 being that your fiscal year starts in April. If it starts June then you need use 6
IF(CalendarTable[CalendarMonthNumber] >= _FiscalYrStrt,
CalendarTable[CalendarYear] + 1,

Hope that helps. Thanks

Friday, February 26, 2021 - 2:58:19 PM - Shar Back To Top (88308)
Great and easy to follow! I've used SSAS to do this in the past, but this is much easier and great for end-users to create. One question - how would you create fiscal years off this data? Say my fiscal year stats April 1 - is there a calculation that would create these for me as well?
Thanks! Shar

get free sql tips
agree to terms