How to Create Today’s Date in Power Query M

Knowing how to create the current date in Power Query is invaluable for your queries. Checking whether a date falls on today’s date, timestamping your refresh or generating a calendar that runs until today are just a couple of use cases. In this article, we will discuss how to create the current date, current UTC date, and location proof date in Power Query.

Table of contents

• Creating the Current Date
  • Today’s Date
  • Today’s Date in a Query
• Current UTC Date
• Date respecting Daylight Savings
• Conclusion

Creating the Current Date

Today’s Date

So, how can you create the date of today in Power Query? The easiest way to return the Date of today is by using the DateTime.LocalNow function. The function looks at the current date and time on your system.

The easiest way to generate the current date in Power Query is by using the DateTime.LocalNow function, which looks at the current date and time on your system.

= DateTime.LocalNow() // Returns the current local datetime value like

You can turn the current date-time value into a date by using either the DateTime.Date or Date.From function.

= DateTime.Date( DateTime.LocalNow() ) // Returns the current date

= Date.From( DateTime.LocalNow() )     // Returns the current date

Today’s Date in a Query

You can easily generate a list of dates dynamically by writing a formula that includes Duration.Days and List.Dates.

= List.Dates( 
    #date(2023, 1, 1 ),
     Duration.Days( Date.From( DateTime.LocalNow() ) - #date(2023, 1, 1 )) +1, 
    #duration( 1, 0, 0, 0 ) 
)

However, this method uses the current date and time on the system, which can give different outputs depending on the location of the system that refreshes. This can be an issue if you need a more predictable value.

That means if your tenant location is in a different country, the DateTime.LocalNow value returns the value of that location. Which can be a different value than you expect or desire. In case you need a more predictable value, you can also make use of another function.

Current UTC Date

To return the same value regardless of the refresh location, you can use DateTimeZone.UtcNow, which returns the current date and time in UTC (or GMT).

= DateTimeZone.UtcNow()

If you compare that with DateTimeZone.LocalNow, the difference is in the time zone part. I’m writing this on a machine with the current time at GMT+1. It shows as follows:

= DateTimeZone.LocalNow()

Date respecting Daylight Savings

Using DateTimeZone values can lead to differences when it does not account for daylight savings. You can account for this by creating a location-proof date that respects daylight savings.

The Daylight Saving system is a date time system that changes the time twice as year. That happens at the last Sunday of March and the last Sunday of October.

Using a datetimezone value and converting it to a date can lead to differences when it does not take into account daylight savings. You can account for this by using this method.

Conclusion

Knowing how to create the current date in Power Query is very useful in making dynamic queries. With the DateTime.LocalNow function, you can quickly generate the current date. However, this method can give different outputs depending on the location of the system that refreshes. By using DateTimeZone.UtcNow or creating a location-proof date that respects daylight savings, you can return a more predictable value. Knowing these techniques can save you time and reduce errors in your data analysis.