Please note javascript is required for full website functionality.
MVP

Blog

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.

In ‘Custom Column’ on the ‘Add Column’ tab, I create my new column.

The M functionality I have used is 

= Date.AddDays([Date], 30)

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.

The M functionality I have used is

= Date.AddMonths([Date], 1)

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.

The M functionality I have used is 

= Date.AddQuarters([1 month due date], 1)

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.

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.

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!

The M functionality I have used is 

= Date.AddYears([Review By], 1)

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!

Newsletter