top of page
Search
    • 4 min read

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

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.


A list of all the dates from the Order Table

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.


In the image above, Start of Month keeps the Date property, but the Month Name is 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.



bottom of page