Create Calendar Table Using Power Query M Language


By:   |   Updated: 2021-02-26   |   Comments (5)   |   Related: More > Power BI


Problem

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.

Solution

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
Date.From(DateTime.LocalNow())

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
{Number.From([StartDate])..Number.From([EndDate])}

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
Date.Year([Dates])

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
Date.Month([Dates])
Diagram showing where to create Custom Column 4
Date.MonthName([Dates])
Diagram showing where to create Custom Column 5
Text.Start([MonthName],3)

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

Diagram showing where to create Custom Column 6
Date.QuarterOfYear([Dates])

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
let
    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])
in
    #"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.


Last Updated: 2021-02-26


get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips



Comments For This Article




Wednesday, March 03, 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.

Thanks.

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
RETURN
IF(CalendarTable[CalendarMonthNumber] >= _FiscalYrStrt,
CalendarTable[CalendarYear] + 1,
CalendarTable[CalendarYear])

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


download





Recommended Reading

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

Power BI Conditional Formatting for Matrix and Table Visuals

Power BI Workspace Permissions and Roles

Power BI Anomaly Detection














get free sql tips
agree to terms