How to use Date.AddDays in PowerQuery
Date.AddDays is a great function that allows us to add days onto a column of Date or DateTime values in the PowerQuery editor. Adding days onto a date value isn’t something that you can do through PowerQuery’s user interface. However, because it’s quite a simple function to use, it’s a great starting point for learning M code.
In the video above I show how this function can be used to create a payment due date column in an accounts payable report where we only have an invoice date column. We could use the same function for an Account Receivable report to show when we’re expecting cash in. Both together would be really useful for a cashflow forecast!
If you’d like to follow along with the video, then download the Excel file in the link to the right, and connect Power BI or PowerQuery in Excel to the file:
A little bit about the function...
The syntax for Date.AddDays is nice and easy. There are two parameters only:
=Date.AddDays(Date or DateTime value or Column, Number of Days to be added)
The function should then return a column of dates being the Date or DateTime value in the first parameter with the number of days in the second parameter added on. The function won't automatically format the returned value as dates though. It returns an 'any' type value, meaning that we can go and designate the column as whatever data type we would like.
While the video describes the Date.AddDays function, this function is part of a group of functions which work in the same way, including Date.AddWeeks, Date.AddMonths, Date.AddQuarters and Date.AddYears. These functions add weeks, months, quarters or years respectively to a date value or column.If you want to use one of the other similar functions such as 'Date.AddWeeks', then you just need to change the name of the function to 'Date.AddWeeks' instead of 'Date.AddDays' and the second parameter will be the number of weeks that you want to add onto the date value or column.
Other Popular Finance Team Training posts on PowerQuery...
- Why is My Merge Not Working in PowerQuery?Excel, Power BI, PowerQueryOctober 28, 2021
- How to VLOOKUP Multiple Matching Rows Using PowerQueryPower BI, PowerQueryJanuary 12, 2021
- How to Connect PowerQuery in Power BI or Excel to a Sharepoint filePower BI, PowerQueryDecember 14, 2020
- How to use Date.IsInNextMonth in PowerQueryPower BI, PowerQueryDecember 2, 2020
- Creating a Pivot Table from Data on Multiple Sheets in an Excel WorkbookExcel, PowerQuery, UncategorizedNovember 11, 2020