
The date plays an essential aspect of the analysis. Insights like understanding customer behavior, finding time series patterns, business growth are a few critical aspects of data analytics.
Experts recommend including a calendar table in the data model. It enables us to view data from multiple perspectives, and we can compare data from different tables on a single timeline.
In Power BI Desktop, we can also add a calendar table using DAX. Refer to the article
Power BI Time Intelligence: Calendar Table
This article explains the steps involved in creating a dynamic calendar table using Power Query. It comes handy while building a data model using Power Pivot.
Creating a calendar table using Power Query categorized into two parts: tricky stuff and easy stuff.
Let us start with the tricky stuff.
Calculating the Start and End Date
The fundamental property of a calendar table is that it holds at least one column with all the dates between a start and end date. For a calendar table to work, we need at least one column with dates in the data tables.
Let us consider the following data table:

For this exercise, our base column in Order Date of the Orders table.
To keep the calendar table dynamic, we create two variables: StartDate & EndDate from Order Date.
Objective 1: Find the Start Date for the Calendar Table
Add a Blank Query
Go to Home > New Source > Other Sources > Blank Query

Click on Advanced Editor under Home

Use the following code under Advanced Editor:
let
Source = Table.SelectColumns(Orders,{"Order Date"})
in
Source

Table.SelectColumns is taking reference from the Orders table and creating a new table with Order Date. The benefit is that the new table is dynamic, and it considers changes in the source table Orders.

Now we apply the following steps:
· Filter to get the earliest date (Start Date)

· Remove duplicates to get unique values for date

· Set the column property to Whole Number

It will convert the date into a number.
Fun Fact: Excel stores all the dates in the form of numbers. The number 1 means January 1st, 1900. Write 1 in an Excel cell and format it as date (use keyboard shortcut CTRL+SHIFT+3)
· Right-Click on the value > Drill Down

· Rename the query as StartDate

Tip: Do not use space while defining query name.

Objective 2: Find the End Date for the Calendar Table
Follow all the steps used in getting the start date with one exception: While filtering, filter to get the Latest Date (instead of earliest date)

Rest all the steps stay unchanged (From start to finish).
Now, we have two variables: StartDate & EndDate

Creating Calendar Table in Power Query
Add a blank query & use the following code:
let
Source = {StartDate..EndDate}
in
Source

In simple words, Power Query reads the statement {StartDate..EndDate} as create a list from StartDate value (39815) to the value equal to EndDate(41274).

When these numbers transformed as dates, it is essentially a list of all the dates between 2-Jan2009 to 31-Dec-2012.
Now we apply the following steps:
· Transform the list to a table. Transform > to Table

· Set the property & rename the column as Date

Now we have a calendar column with a date starting from the minimum Order Date to maximum Order Date

Now it is time for the easy stuff.
Using Date Transformations in Power Query
Ideally, a calendar table should have supporting fields like Year, Quarter, Month. Power Query has easy options for adding different date elements using mouse-click.
Adding Year
Select the Date column > Add Column > Date > Year

Adding Month
Select the Date Column > Add Column > Date > Month > Start of the Month

It adds a column with the first date of the month. For instance, if the date is 3rd January 2020, then it adds a column with the value as 1st January 2020.
Note: I prefer using the Start of Month as compared to other choices as the added column has the date property. It enables sorting and filtering as the date and not a number or a text.

Similarly, we can add columns for Quarters, Week Starting, or Week Ending.

Alternatively, you can use the following M code in the Advanced Editor:
let
Source = {StartDate..EndDate},
#"Converted to Table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(
#"Inserted Year",
"Start of Month",
each Date.StartOfMonth([Date]),
type date
),
#"Inserted Month Name" = Table.AddColumn(
#"Inserted Start of Month",
"Month Name",
each Date.MonthName([Date]),
type text
),
#"Inserted Quarter" = Table.AddColumn(
#"Inserted Month Name",
"Quarter",
each Date.QuarterOfYear([Date]),
Int64.Type
),
#"Added Prefix" = Table.TransformColumns(
#"Inserted Quarter",
{{"Quarter", each "Q" & Text.From(_, "en-US"), type text}}
)
in
#"Added Prefix"
Advantage Power Query:
· Most tasks require minimal, or no-coding.
· It is dynamic and considers updates in data. Power Query applies all the steps performed on the latest data sets (just like running a macro) to produce output.