Search

2 min read

Date Transformations with Power Query

Along with Text Transformations, Power Query offers a variety of options for date transformations. The date plays an essential aspect in any analysis, such as performance comparison from the previous year, quarter, or month. Usually, date columns are part of the fact tables.

In this article, we use Power Query to extract date components with simple mouse clicks.

Sample Data:

Download the sample file

Extracting Time Components

Often date fields have DateTime stamp. Using Power Query, we can quickly format the column as Date

Click on the icon before the column name and select Date

It transforms the existing column as a date and removes the time stamp. In case if we need to store the time stamp, then:

Option 1:

Select the date column > Add Column > Time > Time Only

Transform the date column as Date

Option 2:

Right-click date column > Duplicate Column

Transform the duplicated column as time and the original column as a date.

Transform vs. Add Column

In Power Query, you find the following features commonly available in the two ribbons: Transform & Add Column

Features under Transform

Features under Add Column

There is only one difference: when selected from Transform, it replaces the value of the existing column with the output. At the same time, Add Column gives the output in a separate column.

So, choose wisely!

Extracting Date Components

From a date column, Power Query can extract the following options:

*All the examples in the table below is for the date 20th May 2020

Adding columns from date column using Power Query

Tip: Use Add Prefix (Transform > Format) option to add “Q” in quarters along with the date transformation for the quarter.

Calculating Difference of Two Dates

Calculating the difference between two dates is a simple task in Power Query.

Select the two columns > Add Columns > Subtract Days

Be mindful of the sequence while selecting the columns. In this example, we selected the Ship Date first and then the Order Date; hence Power Query applied the calculation Ship Date – Order Date. In case if we reverse the sequence, the result is the opposite (Order Date – Ship Date).

Calculating the difference from Today’s date

Another frequent requirement is to calculate the difference from today’s date (System date). For calculating the difference between today and a given date:

Select the date column > Transform or Add Column > Date > Age

The output of the earlier step is a Duration column type. Power Query offers extensive features under Duration.

Duration Transformation

Duration is another data type under the DateTime category

The output of the difference between a date and today’s date in Power Query is the Duration data type. It has the following components:

Days| Hours| Minutes | Seconds

Power Query offers features for cumulating the duration into years, days, hours, minutes, and seconds. In this example, the aim is to calculate the total years between the Ship Date and TODAY()

Select the Age column > Transform > Duration > Total Years