Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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
Today using DateTime.LocalNow

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
Turn DateTime into Date Value with DateTime.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.

Caution

The above method uses the current date and time on the system. Due to different timezones, this 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 local datetime 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 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()
Get Current UTC DateTimeZone

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()
Get Current Local DateTimeZone

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.

Tip

The Daylight Saving system is a date time system that changes the time twice a year. In the Netherlands, that happens on 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. 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.

Share on:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.