In a comment on a previous post, a reader asked how you return a specific day of the next month from any given date. In other words, I've got a date of March 5 and I want to use Power Query to return April 10 in Excel (or Power BI). How do you do it?
The Excel User's First Guess
So my first thought was to jump straight into the Power Query Formula reference guide to review the date functions. Surely there must be something in there to manipulate dates and such, right?
Here's a quick list of the the functions I knew I'd need:
- Date.Year()
- Date.Month()
- Date.AddMonths()
So those are awesome for ripping dates apart and shifting them, but what I really needed at the end was a way to put things back together. I needed an equivalent of Excel's =DATE(year,month,day) function. I couldn't find one.
Return a Specific Day of the Next Month
After poking around with this for a while, it suddenly occurred to me that I was doing this all wrong. To return a specific day of the next month, I just needed to provide the "literal" #date() and I was good to go.
Let's take a simple table like this:
I pulled it into Power Query, went to Add Column --> Custom Column, and added the following formula:
=#date(
Date.Year(Date.AddMonths([Dates],1)),
Date.Month(Date.AddMonths([Dates],1)),
10
)
And at that point it works beautifully:
Basically, the #date() literal works just like Excel's DATE() function, you just case it differently and put a # tag in front of it:
#date(year,month,day)
It's a weird one, for sure
Returning a specific day of the next month is one of those odd cases where you have to use one of Power Query's literals to create the date you want, rather than employing a function to convert values as you're used to in Excel. The good news though? Miguel does an amazing deep dive into the M coding language in our Power Query Academy, including explaining what literals, tokens, keywords and more are all about.
If you want to understand this in depth, check out our course:
PS: Sign up for our free trial first, to make sure you like our style! And when you're convinced… you won't find better Power Query training anywhere. 
7 thoughts on “Return a Specific Day of the Next Month”
But... for the 2nd row result is incorrect - it should be 2019-01-10 🙂
Doh! You’re right Maxim! That formula should be:
=#date(
Date.Year(Date.AddMonths([Dates],1)),
Date.Month(Date.AddMonths([Dates],1)),
10
)
How did I not see that? 🙁
Will fix it up in the AM tomorrow. Thanks!
Thanks Ken for fun with dates 🙂
Another solution could be:
Date.EndOfMonth([Daty])+#duration(10,0,0,0)
Of course, you can also do this using only UI (a few steps using the above logic).
Thanks Mr. Ken.
The year don't need add months:
#date(
Date.Year([Date]),
Date.Month(Date.AddMonths([Date],1)),
10
)
Hi Linelson,
It does, or the Dec dates return the incorrect year when rolled forward to Jan. (Dec 17, 2018 becomes Jan 10, 2018 not Jan 10,2019 if you don't add one to the year.)
Hi Ken,
I need extract specific month and year from the column and group it. How can I do that, using power query I inserted a month and year alone, tried giving if condition for throwing the value, but it's not working properly. Any better solution please?
Example:
Dates:
09/31/2018, 11/23/2018, 16/4/2019
Output should be categorised into quarters
09/31/2018 - Q2fy19, 11/23/2018 - Q3fy19 ,16/4/2019 - Q1fy20
Hi Vidhya,
The easiest way will be to add a Year Column and a Month column. Then run some conditional logic to add a new column that adds or subtracts from the year based on the month number. Also, add another column that uses conditional logic to calculate your quarter based on the month. Finally, merge the new quarter and year back to together using "fy" as a delimiter.
Having said that, you've provided dates that are in 3 different formats here:
9/31/2018 is not a valid date as there are only ever 30 days in Sep, so this will return an error when converted to a date
11/23/2018 is in a MM/DD/YYYY format
16/4/2018 is in a DD/MM/YYYY format
One of those two will cause an error when you try to convert it. (I don't know which, as I don't know your default date settings for your system.) You need to clean that data up and standardize on one format before trying to do this.