Power Query is a beneficial tool of Excel for importing or connecting to external data sources, and then editing that data (i.e. removing columns, changing the type of data, merging tables). It allows a user to load the data to the Excel worksheet. However, there are almost 30 date functions in Power Query that you can use with just a button click. Today we are going to see some uses of Power Query Date functions in Excel.
Excel Power Query Date Function: 3 Simple Ways
Let’s consider the dataset below. Here, some products of an online store, their order, and delivery dates are provided.
Based on this dataset, we will try to show 3 common uses of Power Query Date functions in Excel. Let’s check them now one by one!
1. Getting Year, Quarter, Month, Day
Applying the Date function of the Power Query tool, you can extract Year, Quarter, Month, and Day from a list of dates. Let’s consider the order dates from our described dataset. We will try to extract Year, Quarter, Month, and Day from the list of order dates of the products.
For this, proceed with the following steps.
⏩ Steps:
- Then, the Create Table dialog box will come up. Click OK.
- Now, the desired Power Query Editor window will show up. You will see your dataset in that window. The date has been shown with time here.
- In the Order Date column, click the icon (clock-calender icon) at the top-left corner. Click it and select Date from the dropdown.
- After that, you will see a new pop-up named Change Column Type. Here, click Replace Current.
- Hence, the data type will be changed to Date.
- Now, go to the Add Column tab in the Power Query Editor.
- Here, you will see the Date. This icon has the desired Date functions. Click the dropdown.
- Here, in the dropdown list of the Date icon, you will see a couple of functions like Age, Year, Quarter, Month, Week, and Day.
- In order to extract the Year from your date, click the drop-right arrow of the Year option > then select Year.
- As a result, you will see a new column has been inserted mentioning the Year from the date list (i.e. 2020 has been extracted from the date 23-Mar-20).
- If you want to get in which Quarter of the concerned year your date exists, then again go to the Date icon and select Quarter > Quarter of Year.
- You will see the Quarter column beside the Year.
- Repeat the process for extracting Month.
- You will get the Month column added beside the previous column.
- Similarly, find out the Day from the date.
- Now, go to the Home tab of the Power Query window and click Close & Load.
- This command will load the data to the Excel worksheet window.
2. Finding Differences Between Two Dates
Another task you can perform with the Power Query Date function is to find the difference between two dates. Using the Excel Date function, you can perform this task but the Power Query Date function accelerates the speed of performing this task.
Let’s consider our dataset once again.
We want to find the difference between the order date and the delivery date. For this, follow the steps below.
⏩ Steps:
- First, import the data to the Power Query Editor window following the same steps as Method 1.
- Now, select the Order Date column and Delivery Date column together by pressing the CTRL key.
- Then, go to the Add Column tab and click the dropdown of the Date icon > select Subtract Days.
- Hence, you will see that a new column named Subtraction has been inserted calculating the difference between the two dates.
- After that, apply the Close & Load command once again to load this data to the Excel worksheet.
3. Getting Name of Day and Month
Consider the Order Date column from our dataset once again. We will find the name of the day and the name of the month from the ordered date.
⏩ Steps:
- First, import the data to the Power Query window > go to Add Column > click drop-down of Date > select Day > click Name of Day.
- You will see the Name of the corresponding Date has been inserted beside the Date.
- Similarly, find the Month name and add a new column named Month Name.
- Finally, load the data to the Excel window and you’re done.
Practice Section
Here, I am providing you with a practice sheet so that you can practice yourself. Try to find out anything you want by applying the Power Query Date functions.
📁 Download Practice Workbook
You can download the practice book from the link below.
Conclusion
In this article, I have tried to show you some ways to use the Power Query Date functions in Excel. I hope this article has shed some light on your way. If you have any questions, or feedback regarding this article, please don’t forget to share them in the comment box. Happy Excelling!
