June 14, 2020
Dynamic Calendar in Power Query Using an M Function
In this article we’ll create a dynamic calendar in Power Query editor, using a function written in M language.
Calendar table is the most important table of Power BI model. We have seen how it looks in our Calendar in Power BI article. There are several ways to create a calendar table. The steps we used in Dynamic Calendar in Power Query article can be stored in a function, and used anytime we need to create a calendar table in our data model.
M Language
In our last article we made a dynamic calendar in Power Query. All the steps used to transform data in Power Query were stored in Applied Steps section. Every step generates a formula in M language and we can see the syntax in the formula field. We’ll need a calendar table in every data model, so it’s useful to use a pre-written function.
Creating dynamic calendar
We start the same way as we did in our last article. We connect to the data and open it in Power Query. There are several date columns in our data this time. We’re only interested in Order Date and Payment Date in DateNTime table.
Data table with two relevant date fields.
We have to beware of birthday data when creating a calendar table. We usually don’t want birth dates in our calendar, because that would mean a much larger calendar table with a lot of irrelevant dates. Birthdays are usually not relevant to business data, so we can leave them out when creating a calendar table.
We again want to merge Order Date and Payment Date into a single column. We create a reference by right-clicking DateNTime table and selecting Reference. We name the reference Calendar. We remove other fields by right-clicking on Order Date and selecting Remove Other Columns. We then calculate beginning of the year by right-clicking on the column and selecting Transform > Year > StartOfYear. We also remove the duplicates by right-clicking and selecting Remove Duplicates. We rename the field to Date. We create and edit reference for the Payment Date field in the same DateNTime table in the same way. We then join the references: we select Append Queries in Calendar table and select the other table, named Calendar1 in our case. We again remove the duplicates. We are left with starts of dates from both starting columns. We rename this new field to Date.
Both relevant date fields, merged to one field.
Now we can create a new query using a function, that will generate all the dates between the starting and ending year of the column. In the Queries window we right-click and select New Query > Blank Query. We then open Advanced Editor and paste the function below:
| (TableName as table,ColumnName as text)=>
let Source = TableName, #”Removed Other Columns” = Table.SelectColumns(Source,{ColumnName}), #”Renamed Columns” = Table.RenameColumns(#”Removed Other Columns”,{{ColumnName, “Date”}}), #”Calculated Start of Year” = Table.TransformColumns(#”Renamed Columns”,{{“Date”, Date.StartOfYear, type date}}), #”Removed Duplicates” = Table.Distinct(#”Calculated Start of Year”), #”Duplicated Column” = Table.DuplicateColumn(#”Removed Duplicates”, “Date”, “Date – Copy”), #”Renamed Columns1″ = Table.RenameColumns(#”Duplicated Column”,{{“Date – Copy”, “Date2″}}), #”Calculated End of Year” = Table.TransformColumns(#”Renamed Columns1″,{{“Date2″, Date.EndOfYear, type date}}), Base = Table.TransformColumnTypes(#”Calculated End of Year”,{{“Date”, Int64.Type}, {“Date2″, Int64.Type}}), MinDate = List.Min(Base[Date]), MaxDate = List.Max(Base[Date2]), Custom2 = {MinDate..MaxDate}, #”Converted to Table” = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #”Renamed Columns2″ = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}), #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns2″,{{“Date”, type date}}), #”Inserted Month” = Table.AddColumn(#”Changed Type”, “Month No”, each Date.Month([Date]), Int64.Type), #”Inserted Year” = Table.AddColumn(#”Inserted Month”, “Year”, each Date.Year([Date]), Int64.Type), #”Inserted Month Name” = Table.AddColumn(#”Inserted Year”, “Month”, each Text.Upper(Text.Start(Date.MonthName([Date]),3)), type text), #”Inserted Quarter” = Table.AddColumn(#”Inserted Month Name”, “Quarter”, each “Q”&Text.From(Date.QuarterOfYear([Date])), type text), #”Added Custom” = Table.AddColumn(#”Inserted Quarter”, “YM”, each if [Month No]>9 then “-” & Text.From([Month No]) else “-0″ & Text.From([Month No])), #”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Year Month”, each Text.From([Year])&[YM]), #”Removed Columns” = Table.RemoveColumns(#”Added Custom1″,{“YM”}), #”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”,{{“Year Month”, type text}}), #”Inserted Day of Week” = Table.AddColumn(#”Changed Type1″, “Weekday No”, each Date.DayOfWeek([Date]), Int64.Type), #”Inserted Day Name” = Table.AddColumn(#”Inserted Day of Week”, “Weekday”, each Text.Upper(Text.Start(Date.DayOfWeekName([Date]),3)), type text), #”Inserted Week of Year” = Table.AddColumn(#”Inserted Day Name”, “Week of Year”, each Date.WeekOfYear([Date]), Int64.Type), #”Added Custom2″ = Table.AddColumn(#”Inserted Week of Year”, “Custom”, each if [Week of Year]>9 then Text.From([Week of Year]) else “0”&Text.From([Week of Year])), #”Added Custom3″ = Table.AddColumn(#”Added Custom2″, “Week”, each “W”&[Custom]), #”Removed Columns1″ = Table.RemoveColumns(#”Added Custom3”,{“Week of Year”, “Custom”}) in #”Removed Columns1″ |
M function that creates a table of all the dates for a selected field. We have a column of the first dates for the years between 2000 and 2020 in our example. Function will generate a table of all the dates between 1.1.2000 and 31.12.2020. Table will also contain date attributes like day, week, querter and year.
We confirm the entry with Done. Function we created now has two entry fields for parameters:
M function offers a list of parameters.
For TableName we select table Calendar and for ColumnName we enter column name Date. We then click Invoke. Function creates a query and a table named Invoked function, that contains all the dates from the beginning of the earliest to the end of the latest year. Table also includes date attributes like day, week, month, quarter and year.
Finished calendar table.
Data is now ready. Before loading the data to a model, we turn off loading for the tables that are not relevant for us. In this case, these are tables Calendar and Calendar1. We right-click on each table and remove the tick at Enable load. Tables are now written in italic font, meaning they will not be loaded to the model.
We disable loading for the tables that are not relevant.
Finally, we import data with Close & Apply.
Why M function is a better choice than CALENDARAUTO()
CALENDARAUTO() reads all the dates in our data. If we have birthday dates in our data, we will get a very large calendar table that will slow down our data model. This is why using M function is a better way to create a dynamic calendar table.
Another problem with CALENDARAUTO() is that it also reads time and treats it as a date. Every time data will be treated as a date 0.1.1900, since this is the first day Power BI uses to count dates. Using CALENDARAUTO() will generate a calendar table of dates starting with 1.1.1899 (since 0.1.1900 doesn’t exist). We’ll discuss more about date and time in Power BI in our next article. In conclusion, method described in this article gives us more control than other methods or functions.