This article demonstrate how to get the difference between to dates in Power Query.
You can use the Power Query Duration function that is Duration.Days to get the difference between two dates in days.
As you can see, here we have a sample data which contains the customer order details.
Lets say, you have a requirement to get the Order Aging to see how many customer has got the delivery on time that is On or before 5 days and later that is after 5 days.
So, the first step is to get the days difference between order date and delivery date.
To get the days difference we can use Power Query Duration function that is Duration.Days.
Lets create a custom column first as shown below.
Go to Add column then click on Custom Column tab.
Once you click on Custom Column tab, you will see a popup window named Custom Column appears as shown below.
Provide the name for your column, and then write the function to get the difference between two dates that is Duration.Days and you will see Power Query suggest you the function name based on matching string that you type.
Lets complete the function and after that click on OK button as shown below.
=Duration.Days([Delivery Date] - [Order Date])
Once you click on OK button, you will see it returns the days difference between order date and delivery date as shown below.
Now, lets change the type of this column to whole number as shown below.
So far, we have seen how to get the day difference between two dates using Power Query.
Now you can add one conditional column or custom column and easily check how many customer got the order delivery on time that is on or before 5 days or later that is after 5 days.
Lets quickly create one custom column name it Order Aging and write condition if days difference is less than or equal to 5 then delivered On time else Late delivered as shown below.
if [Days Difference] <=5 then "Delivered On time" else "Late Delivered")
After that, click on OK button and you will see the Order Aging based on Days Difference.
Also Read..
201 total views, 2 views today