Calculating the difference between two dates in Power Query can be done using a Power Query formula that references two date columns. You can use the same technique to calculate the difference between a date column and today’s date.
Table of Contents
Getting Started with Date Calculations in Power Query
Before getting started, you will have to have data loaded into Power Query. In our example we’ll use a simple dataset that contains multiple date columns for sales, invoice, and invoice due date along with an order number. This is a pretty common setup that comes up when calculating receivables aging or other accounting use cases.
Our dataset is as follows.
While we already have our columns defined with a Date Data Type, it’s important to make sure that the columns you’re working with are defined as dates.
If you try calculating the day difference between text or numeric columns, your formulas will result in an error.
Ensure That Columns are Correct Data Type
Data types are noted at the top of each column by a small icon. Numbers are numeric columns, calendars are dates, text are text etc.
If you need to change a column data type, right click on the column header and select Change Type.
After making the change, you will see a new Applied Step on the right side of Power Query that tracks the change.
Create a New Custom Column
Power Query M formulas can either be entered into the formula bar above your column headers, but this technique is rarely used other than by pro Power Query developers. The more common method is to create a new column with your formula in it.
To create a new column based on a Power Query formula, go to the Add Column section of the Power Query ribbon and select Custom Column.
This will create a new column, but equally important it will bring up the Power Query formula dialog box.
The Power Query Formula for Date Difference
The Power Query formula to calculate the difference between two dates is Duration.Days. To use it, you have to create a Custom Column which allows you to enter formulas using the Power Query M language. Formulas are a different syntax from Excel and Power BI DAX.
When working with two pre-existing date columns the syntax is:
Duration.Days([EndDate] - [StartDate])
In practice, the Power Query formula will look like the following screenshot, where we calculate the day difference between two date columns, the Invoice Date and the Sales Date.
When you click OK a new column will be created in the dataset that can later be used for binning, analyzing or presenting in various visualizations.
Convert Days to Alternative Units (Optional)
You can further perform additional calculations after determining the number of days between two dates, such as dividing by 365 to determine the number of years or you could multiply by 24 to get the number of hours. Simply surround the first part of the calculation in parenthesis and add the mathematical function needed at the end of the formula.
Power Query also supports additional duration functions, such as: duration.hours, duration.minutes, duration.seconds if you need more precise calculations. You can learn more about duration functions from Microsoft here: Duration functions – PowerQuery M | Microsoft Learn
Calculating the Difference Between a Date and Today
If you want to calculate the difference between two dates when one of the days is today, you will need to use a formula to pull in today’s date. Create a Custom Column and use the Power Query M formula DateTime.LocalNow() to create a column showing today’s date.
Alternatively, you could embed the formula in with a longer formula, but most of the times we prefer to separate the calculations into separate steps for easier understanding if someone else has to manage the data model.
The following screenshot is using the DateTime.LocalNow formula to create a column of today’s date and time.
After creating a new column, don’t forget to convert the data type to a Date or Date Time column by right clicking on the column header and changing the data type to avoid errors in subsequent calculations.
Once the new column with today’s date is entered in, you can apply the standard Duration.Days formula to calculate the number of days between a date and today.
Conclusion
Power Query is an incredibly useful data transformation tool from Microsoft. Power Query is available in Excel, Power BI, and Microsoft Fabric as a way to prep data for further analysis and presentation. The ability to calculate the difference between date columns or to calculate the difference between a date column and today’s date is quick and easy.
Use the Duration.Days formula to calculate the difference between two columns, but first make sure that each column is in the appropriate format and can be defined as a date or date time data type. Sometimes you need to transform the column to get it into the right format for Power Query to read it appropriately.