Power Query: Changing Dates
27 June 2018
Welcome to our Power Query blog. This week I look at some useful ways to manipulate dates in M.
I will take a look as some Date() M functions that can be used to create dates from an existing date, for example, by adding days or weeks to the original date. I will give an example for each function.
Date.AddDays
Date.AddDays(datetime, days as number)
Returns a Date / DateTime / DateTimeZone value with the day portion incremented by the number of days provided. It also handles incrementing the month and year portions of the value as appropriate.
Below, I have some expense data from my reliably non-existent salespeople. It is in their contracts that they will be reimbursed within 30 days of an expense being incurred, so I need to calculate when this date will be.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image1.png/2df4c02ca71a8efd3dac373707b9774e.jpg)
In ‘Custom Column’ on the ‘Add Column’ tab, I create my new column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image2.png/cf7cf0a183d6a8aeac22398b3e19e972.jpg)
The M functionality I have used is
= Date.AddDays([Date], 30)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image3.png/5f597bec21472b5d63386d7efddd85df.jpg)
I can now see the deadline of when I should pay each expense.
Date.AddMonths
Date.AddMonths(datetime as datetime, n as number) as nullable datetime
Returns a DateTime value with the month portion incremented by n months.
If the salespeople’s contracts showed that they must be paid for their expenses within a month of incurring them, I could use this M function to calculate the due date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image4.png/03a4ba4e4501809f762c9a5e261b76d3.jpg)
The M functionality I have used is
= Date.AddMonths([Date], 1)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image4.5.png/65c9f7366c291e66ed4d5e214045c834.jpg)
The new due date has been calculated correctly.
Date.AddQuarters
Date.AddQuarters(datetime, quarters as number)
Returns a Date / DateTime / DateTimeZone value incremented by the number of quarters provided. Each quarter is defined as a duration of three months. It also handles incrementing the year portion of the value as appropriate.
I have decided that the payment will be marked as ‘aged’ once we reach the same position in the next quarter. To determine the date this will happen, I add a quarter to my due date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image5.png/cd6d8191c9c019cbede27ceb7e7830dd.jpg)
The M functionality I have used is
= Date.AddQuarters([1 month due date], 1)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image6.png/ae631eef13b87a0c54202a71a3348495.jpg)
The aged debt date has been calculated correctly.
Date.AddWeeks
Date.AddWeeks(datetime, weeks as number)
Returns a Date / DateTime / DateTimeZone value incremented by the number of weeks provided. Each week is defined as a duration of seven days. It also handles incrementing the month and year portions of the value as appropriate.
I can also increment my date by a number of weeks, so I’ll use this functionality to create a date to review the expense payment.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image7.png/bdd917cddc7c3dcaad085f9d20b197ee.jpg)
The M functionality I have used is
= Date.AddWeeks([#"Aged Debt Date (from 1 month due date)"], 2)
So, I will review if the payment has been made two (2) weeks after it becomes an aged debt.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image8.png/7d0fe13851c240eafaa642e3b203b3f0.jpg)
I can now see the review date.
Date.AddYears
Date.AddYears(datetime as datetime, years as number) as datetime
Returns a DateTime value with the year portion incremented by years.
This of course is when I will actually pay the expenses!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image9.png/fdcdc1ce8541c64fe42724943a10557f.jpg)
The M functionality I have used is
= Date.AddYears([Review By], 1)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2018/power-query/06-jun/82/pq-82-image10.png/fc2dece4649a88350e288b9c19bb3cac.jpg)
I can now see when the poor salespeople will finally be paid their expenses.
Come back next time for more ways to use Power Query!