Date functions helps you to create and manipulate the part of date such as date, datetime, and datetimezone values. There are various inbuilt date functions available in Power Query that you can use to manipulate your dates as per you requirements.
Lets see the various dates functions available in Power Query. As you can see, here we have a data which includes customers order details.
We will use this data to explore Power Query dates functions.
Adding days to Date
To add the number of days to the date, you can use Power Query below date functions.
This function returns the new date, datetime, or datetimezone after adding a number of days to the datetime.
Date.AddDays(dateTime, numberOfDays)
dateTime: This date, datetime, or datetimezone value to which days are being added.
numberOfDays:Â This is number of days to add in date, an integer value.
Lets create a new column in table, that will show the new date after after adding 3 days to the order date.
Go to add column, and click on Custom column a shown below.
Now, you will see a Custom Column Pop up window opens, now given the column name and write below M Code as shown below.
Once you start writing, an intellisense suggest you function name based on matching inputs.
=Date.AddDays([Order Date], 3)
Now you can see, it creates a new column which have new dates after adding 3 days to Order Date.
After that, you can change the type to date for newly created column.
Lets see, understand Power Query M code behind this.
= Table.AddColumn(#"Changed Type", "Add days", each Date.AddDays([Order Date], 3))
You can, see Power Query adds new column to the table using Table.AddColumn function and execute the date function Date.AddDays for each row in a table.
Note that, Changed Type was the last step in Power Query, so it adds new column after that steps.
You can also substract numbers of days to the dates, for that just use minus (-) before the number of days as shown below.
=Date.AddDays([Order Date], - 3)
Adding Months to Date
To add the number of months to the date, you can use Power Query below date functions.
This function returns the new date, datetime, or datetimezone after adding a number of months to the datetime.
Date.AddMonths(dateTime, numberOfMonths)
dateTime: This date, datetime, or datetimezone value to which months are being added.
numberOfMonths:Â This is number of months to add in date, an integer value.
Lets add 2 months to order dates using Date.AddMonths Power Query Date function using below code.
Just repeat the same step, add custom column and write below Power Query M code.
=Date.AddMonths ([Order Date], 2)
Lets see the output, and you can see it adds 2 months to the order date.
You can also substract numbers of months to the dates, for that just use minus (-) before the number of months as shown below.
=Date.AddMonths ([Order Date], -2)
Adding Years to date
To add the number of Years to the date, you can use Power Query below date functions.
This function returns the new date, datetime, or datetimezone after adding a number of Years to the datetime.
Date.AddYears(dateTime, numberOfYears)
dateTime: This date, datetime, or datetimezone value to which Years are being added.
numberOfYears:Â This is number of Years to add in date, an integer value.
Lets add 5 years to the order date, using below date functions.
=Date.AddYears([Order Date], 5)
You can see, it adds 5 years to the order date and returns new date after adding 5 years.
Extracting day from date
To extract the day from date you can use below date function. It returns the day part of the given date, datetime, or datetimezone value.
Date.Day(dateTime )
Lets extract the day from order date using below code.
=Date.Day([Order Date])
You can see, it extract the day part from Order date.
Extracting Month from date
To extract the month from date you can use below date function. It returns the month part of the given date, datetime, or datetimezone value.
Date.Month(dateTime )
Lets extracts the month from order date, just add new custom column and write below code.
=Date.Month([Order Date])
You can see, it extract the Month part from Order date.
Extracting Year from date
To extract the year from date you can use below date function. It returns the year part of the given date, datetime, or datetimezone value.
Date.Month(dateTime )
Lets extract the year from order date using below code. Add new custom column and write below code.
=Date.Year([Order Date])
You can see, it extracts the Year part from Order date.
Number of days in a month
To see the number of days in a month, you can use below date function, it returns the number of days in the month in the date, datetime, or datetimezone value.
Date.DaysInMonth(dateTime)
dateTime: A date, datetime, or datetimezone value for which the number of days in the month is returned.
To see the number of days in a month for order date, Lets add a new custom column and write below function.
=Date.DaysInMonth([Order Date])
You can see, it extracts the number of days in a month for Order date.
Last day of the month
To see the dates which contains the last day of the month, you can use below date function, it returns the last day of month in the date, datetime, or datetimezone value.
Date.EndOfMonth(dateTime)
dateTime: A date, datetime, or datetimezone value from which the last day of the month is evaluated.
To see the last day of month for order date, Lets add a new custom column and write below function.
= Date.EndOfMonth([Order Date])
You can see, it extracts the dates which contains the last day of month for order date.
Start day of the month
To see the dates which contains the first day of the month or start day of month, you can use below date function, it returns the start day of month in date, datetime, or datetimezone value.
Date.StartOfMonth(dateTime)
dateTime: A date, datetime, or datetimezone value from which the start day of the month is evaluated.
To see the start day of month for order date, Lets add a new custom column and write below function.
= Date.StartOfMonth([Order Date])
You can see, it extracts the dates which contains the start day of month for order date.
Extracting month name from date
Number of days in a month
To see the month name from given date, you can use below date function, It returns the name of the month for given date.
You can also provide a culture such as en-Us, it is an optional.
Date.MonthName(dateTime, optional culture)
dateTime: A date, datetime, or datetimezone value from which month name is extracted.
To see the month name for given order date, Lets add a new custom column and write below function.
=Date.MonthName([Order Date])
You can see, it extracts the month name for Order date.
Extracting the day of week
To see the day of week for given date, you can use below date function, It returns a number from 0 to 6 indicating the day of the week of the given date.
Date.DayOfWeek(dateTime, optional firstDayOfWeek)
dateTime: A date, datetime, or datetimezone value which is used to extract the day of week.
firstDayOfWeek: An optional, A Day value indicating which day should be considered the first day of theweek. Values that it allows as Day.Sunday, Day.Monday, Day.Tuesday, Day.Wednesday, Day.Thursday, Day.Friday, or Day.Saturday.
If you do not provide the value for the firstdayoftheweek, a culture-dependent default is used.
To see the day of the week for given order date, Lets add a new custom column and write below function.
= Date.DayOfWeek([Order Date])
You can see, it extracts the day of the week from Order date.
Note that, we have not provided the first day of week value so it uses the culture dependent default value.
Lets say, the week start day is Sunday, now lets create a new custom column and named it as Day of week 1.
Now provide the value for the first day of week in function that is Day.Sunday as shown in below code.
 = Date.DayOfWeek([Order Date], Day.Sunday)
Now, you can see this time it returns the day of the week considering the Sunday as first day of week.
Extracting the day of week Name
To see the day of week name for given date, you can use below date function, It returns day of week name for given date.
Date.DayOfWeekName(dateTime, optional culture)
dateTime: A date, datetime, or datetimezone value which is used to extract the day of week name.
Culture: An optional, you can provide the culture such as en-US. If not provided it uses the culture dependent default value.
To see the day of the week for given order date, Lets add a new custom column and write below function.
= Date.DayOfWeekName([Order Date])
You can see, it extracts the day of the week name from Order date.
Also Read..
Difference between two dates in Power Query
