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.

Creating the Current Date in Power Query

Today’s Date

To create the current date in Power Query, you can enter the expression “= DateTime.Date( DateTime.LocalNow() )” in the Formula Bar.

So how does that work? We start out by generating the current datetime value in Power Query by using the DateTime.LocalNow function. This operation 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 ) 
)

The output of this code is a list of dates starting at January 1, 2023 and ranging up to today.

In case you need a more predictable value, you can also make use of another function which we will cover next.

Current UTC Date

The easiest way to return the current UTC date is by using DateTimeZone.UtcNow. This operation returns the current date and time in UTC (or GMT), regardless of the location where you refresh.

= 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.

Using a datetimezone value and converting it to a date can lead to differences when it does. For more specific measurements may want to account for daylight savings.

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.