top of page
Search

# 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.

## 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

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.

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.

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 Start of Month",
"Month Name",
each Date.MonthName([Date]),
type text
),
#"Inserted Month Name",
"Quarter",
each Date.QuarterOfYear([Date]),
Int64.Type
),
#"Inserted Quarter",
{{"Quarter", each "Q" & Text.From(_, "en-US"), type text}}
)
in