You can either create a Custom Column in Power Query using Power Query M to calculate the difference between two date columns, or you can use Power Query’s Column from Example feature to let the software generate the formula to determine the number of periods between two dates for you.
Table of Contents
Column Data Types Matter
Before creating a Custom Column or using Column from Example to calculate the difference between dates in Power Query, make sure that columns are defined with date or date time data types. Time intelligence functions only work with these two data types. Even if a column looks like a date, it could be defined as text which will cause calculations to fail.
You can determine the data type of a column by looking at the icon at the top of the column.
Dates will show as a small calendar. If you need to change the data type of a column, right click on the column header and select the new data type.
Calculate the Difference Between Dates in Power Query
To calculate the difference between dates in Power Query, create a new Custom Column. Then use the Duration.Days formula to calculate the number of days between two dates. Power Query M supports several other duration functions to perform the calculation in different units.
Here are the steps in detail:
Step 1.) Create a New Custom Column
From the Power Query editor, navigate to the Add Column section, and click on the Custom Column button. This is how you add additional columns to Power Query.
Step 2.) Use the Duration.Days Power Query M Function
After selecting create a new Custom Column, the Power Query formula editor will appear. This dialogue box allows you to enter Power Query M formulas and functions.
The formula and syntax to calculate the difference between dates in power Query is:
Duration.Days([End Date] - [Start Date])The [End Date] and [Start Date] fields will be replaced with the names of the columns in your data, surrounded by square brackets.
The following example shows the formula being used to create a new column using real column names.
A great part of using Power Query to calculate the difference between two dates is that it works the same for Power BI as it does in Excel.
Step 3.) 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
Using Column from Example for Date Calculations in Power Query
Creating Columns from Example is one of our favorite features of Power Query. It lets you quickly get the results you need without having to create a formula of any kind.
After launching the Power Query editor, you can follow these steps.
Step 1.) Select Columns
Select both columns that you want to calculate the number of days between in Power Query. You can select multiple columns by holding down Shift and clicking on each one.
Step 2.) Click on Column From Examples
From the Add Column section of the Power Query Ribbon, click on the Column from Examples button. When using this feature, we need to tell Power Query which columns are important to look at. You can choose the entire dataset or to use the selected columns.
Step 3.) Provide Examples for AI Formula Recommendations
On the far right of your dataset, there will be a blank column. Type examples of the desired output into this field and Power Query will automatically recommend formulas. Double Click Into the Empty Field and type the desired result.
As you type, formulas and results will be recommended as possible options.
You can either select the calculation from the dropdown menu, or type in an example of the output.
In our example, we typed the Number 4 in the column to the far right a couple of times and Power BI generates and suggests a Power Query formula to identify the number of days between two dates.
Step 4.) Accept a Recommended Formula
When you find a formula that meets your needs, press Enter. In the example above, you can see that the number of days between two dates are automatically filled in and at the top of Power Query, a Duration.Days formula is recommended.
This technique works great for simple datasets, and can be a starting point for writing formulas when you’re new to Power Query M functions.