You can use a number of methods to calculate today’s date in Power Query and then utilize it for many common calculations, such as the difference between two dates or adjusting it so that it’s -1 day for yesterday or -7 days for a week ago. Date calculations are incredibly useful for analysis and advanced Power Query filtering.
We’ll explain how to work with today’s date in Power Query using Power Query M Formulas, custom columns, the difference between common formulas, and some common formulas that work along with it such as calculating the time between dates and quickly parsing date formats when reports provide data that’s difficult to work with.
Table of Contents
Power Query Custom Columns
To get started with Power Query Formulas, you’ll need to launch the Power Query Editor and create a Custom Column. To create a Custom Column, you can use the “Add Custom Column” button from the Add Column tab of the Power Query Editor. This launches a dialog box to enter Power Query M Formulas.
Unlike Excel, Power Query utilizes a different formula language called M. It’s optimized for working with larger datasets but does have a learning curve as the syntax is different than traditional Excel calculations.
An alternative to using the Custom Column button is to type Power Query M code directly into the Fx bar at the top of the Power Query Editor.
Power Query M Formulas for Today’s Date
The DateTime.LocalNow() function returns the current date and time in Power Query. It is often utilized for adding a timestamp to your data or calculating the difference between dates. To extract only the date component, you can use the DateTime.Date(DateTime.LocalNow()) function.
The example below shows a regular DateTime.LocalNow() Formula being utilized to create a new column with the current date and time.
After creating the new Custom Column, it will appear as an applied step and on the far right side of the Power Query Editor.
Don’t forget to right click on the column header and assign a Date or DateTime data type to avoid calculation errors or incorrect sorting. Formulas often result in a mixed value data type even though the data being presented looks like a date.
Converting DateTime to a Date in Power Query
DateTime can be converted to a Date with a DateTime.Date() formula, or you can change the data type of the column. After creating a new Date or DateTime column in Power Query, make sure to right click on the new column name and assign it to a Date or DateTime data type to avoid calculation errors.
To convert a DateTime column to a date, you can also use the he
DateTime.Date() Power Query M function. It strips away the time component, leaving you with just the date. This conversion can be useful for date-based calculations and comparisons that can otherwise be skewed or grouped incorrectly for analysis.
The Different DateTime Functions of Power Query
The following chart explains the differences and use cases for the different DateTime functions available in Power Query. Understanding and being aware of the different formulas can help avoid errors and speed up data transformations for various use cases.
|Returns the current date and time in the local time zone.
|Returns the current date and time in Coordinated Universal Time (UTC).
|Converts a value to a DateTime value. The value can be a date, text, number, etc.
|Extracts the date part from a DateTime value, removing the time component.
|Extracts the time part from a DateTime value.
|Returns the day of the month from a DateTime value.
|Returns the month number from a DateTime value.
|Returns the year from a DateTime value.
|Returns the day of the week from a DateTime value.
|Returns the day of the year from a DateTime value.
In the examples above, DateTime is a place holder for another column name which is typically wrapped in square brackets.
Calculating End of Month and Other Power Query Quick Calculations
Power Query has a number of pre-made transformations to help with date calculations, such as converting a column to the first day of the month or the last day of the month. Right click on a Power Query Date column and select Transform, and the date calculation desired.
When combined with a DateTime.LocalNow() function, you can quickly calculate the first or last day of the month based on today’s date using a combination of Power Query steps.
Calculating the Difference Between Days in Power Query
To calculate the number of days between two dates in Power Query, first make sure that both the starting and end date columns are assigned to a Date datatype. Then create a Custom Column using the Duration.Days() Power Query M Formula to subtract the difference between two columns.
The following example using Duration.Days([StartDate]-[EndDate]) to perform the calculation.
The result is a new column that shows the numeric value of the date difference between two columns.
Today’s Date Using Column from Example
A less commonly used feature in Power Query is the ability to create new columns based on examples. If you infrequently use Power Query or can’t remember formulas it can be a big time saver. From the Power Query Editor select Column From Examples from the Add Column section of the Power Query Ribbon.
An interface will appear with an area on the right to enter in the desired output. In the example below, we began typing in today’s date and Power Query recommends potential formulas and results. When we select Today’s date, the rest of the column auto-fills.
After clicking OK a new column will be created with today’s date without having to write a single line of code! The Power Query Column From Examples feature is incredibly useful, though it can struggle with complex multiple step formulas.
Power Query gives users a number of different was to create a column with today’s date and calculate the difference between dates with common scenarios. You can also use the Column from Example editor to automatically generate columns without having to know any Power Query M Formulas.
Whenever you create date columns, don’t forget to adjust the data type. Many of the formulas such as calculating the difference between two dates will only work when columns are assigned with date data types. Data type mismatches can also lead to improper date sorting and other unexpected behaviors.