How to perform a DATEDIFF for POWER QUERY? The code to build your custom Power Query function is in this article.
- Copy the code of the 5 functions
- Paste them in 5 custom function
- Call the custom DATEDIF function
Copy each code in a different query
For each following code, you must write them in a Blank query (Get Data > From Other Source > Blank Query)
And you will paste the codes in the Power Query editor (Home > Advanced Editor)
The DATEDIFF function code for Power Query
The following codes return the five options of the DATEDIFF function.
Function fn_D
This is the easiest parameter to build
(date1, date2) =>
let
Source = Duration.Days(date2 - date1)
in
Source
Function fn_Y
I have just applied the standard Power Query steps to calculate the number of years.
- Difference between the 2 dates in days
- Convert the type of the column in Duration
- Return the duration in Years
(date1, date2) =>
let
Source = Duration.Days(date2 - date1),
TheDuration = (Duration.TotalDays(Duration.From(Source)) / 365),
YearResult = Number.RoundDown(TheDuration)
in
YearResult
Function fn_YM
Then, we calculate the number of months in the current year (parameter YM). Here, we must consider if the day and the month are greater or lower between the 2 dates.
(date1, date2)=>
let
GapDay = Date.Day(date2) - Date.Day(date1),
GapMonth = Date.Month(date2) - Date.Month(date1),
NbMonth = if GapDay < 0 then GapMonth - 1 else GapMonth,
YM = if NbMonth < 0 then (12 + NbMonth) else NbMonth
in
YM
Function fn_MD
Here, I return the number of days in the current month (MD) and there is a difference with the DATEDIF function of Excel.
The logic here is to 'build' a date with the last day of the month of the date1. If the first day is lower than the second day, I simply subtract the 2 values. But if Day1 is greater, then I calculate the difference between the last day of the month and date1 and finally add the number of Day2
(date1, date2)=>
let
Day1 = Date.Day(date1),
Day2 = Date.Day(date2),
DayEndMonth = Date.Day(Date.EndOfMonth(date1)),
MD = if Day1 <= Day2 then Day2 - Day1 else (DayEndMonth-Day1)+Day2
in
MD
Function fn_M
To calculate the number of months between the 2 dates, I need to call the function fn_Y and multiply by 12, and then, add the result of the function fn_YM.
(date1, date2) =>
let
Step1 = fn_Y(date1, date2)*12,
Step2 = Step1 + fn_YM(date1, date2)
in
Step2
Function fn_YD
Here again, there could be a difference with the function DATEDIF but this parameter is practically never used (I used it once in 20 years). Anyway, the logic here is to build a date with the year of date2 and the day and the month of date1. If the new date is greater than date2, I rebuild a date for the previous year. Finally, I just need to subtract the 2 dates to return the number of days in the year.
(date1, date2)=>
let
Year2 = Date.Year(date2),
BuildDate = #date(Year2, Date.Month(date1), Date.Day(date1)),
TestNewDate = if BuildDate < date2
then BuildDate
else #date(Year2 - 1, Date.Month(date1), Date.Day(date1)),
YD = Duration.Days(date2 - TestNewDate)
in
YD
DATEDIF for POWER QUERY
Finally, I include all these functions inside another Power Query function called fn_DateDif
(date1, date2, ParamDate)=>
let
ValParam = Text.Upper(ParamDate),
ResultFunction = if ValParam = "D" then fn_D(date1, date2)
else if ValParam = "Y" then fn_Y(date1, date2)
else if ValParam = "M" then fn_M(date1, date2)
else if ValParam = "YM" then fn_YM(date1, date2)
else if ValParam = "MD" then fn_MD(date1, date2)
else if ValParam = "YD" then fn_YD(date1, date2)
else "Wrong parameter"
in
ResultFunction
When you have copied all the codes, you have this
Application in Excel
Now in Power Query, you need to call the function with the menu Add Column > Invoke Custom Function.
Then, you call the fn_DateDif function and fill in the 3 parameters like in this example.
And when you load the data in Excel, you have this result
The difference with the Excel DATEDIF function
When you test the Power Query function with the Excel function, the parameter MD will have different results.
It's because the logic to calculate this parameter isn't the same for Excel. For the first example, Excel adds 12 years and 3 months to the first date. So the 'new date' is 22/05/2017. And in this situation, 09/06/2017 - 22/05/2017 = 18 days. In fact, the difference could be explained by the number of leap years.