Dynamic Calendar Table using Power Query – Date Transformations

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.

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:

Sample data file

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.

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.

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

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.

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.

Select the Date column > Add Column > Date > Year

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.