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.
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:
Select the date column > Add Column > Time > Time Only
Transform the date column as Date
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
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 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
Power Query proves handy in quickly transforming large datasets and offers no-code solutions for applying calculations.
Check more articles on Power Query: