There are various functions and formulas for using today's date in Power Query, for analysing data in Power BI or Microsoft Excel. Find out everything you need to know about the Power Query Date function.
In the field of analysis, the date is an important piece of information. It contextualises the data in terms of time.
It is therefore an element that must be taken into account to better understand trends, patterns and variations. This makes it possible, for example, to analyse changes in sales, purchasing frequency or productivity within an organisation.
In the course of their work, analysts regularly need to use today’s date to compare the current trend with a period, or for project management purposes.
In Excel and Power BI, the Power Query data transformation tool allows you to use today’s date in a number of ways.
Power Query and today's date: what's the point about the Power Query Date function?
Firstly, Power Query users can create dynamic filters that automatically reflect the current date.
This can be useful when working with data from the current period. For example, to focus on analysing sales for the current month.
It is also possible to track changes in data over time by creating charts and pivot tables based on the current date.
For automation purposes, dynamic columns reflecting the current date can be used to create daily reports or update data regularly.
Finally, to track the progress of a project and delivery times in real time, you can also create dynamic columns showing both start and finish dates.
Power Query date function for today's date
There are two main functions for the current date in Power Query: Date.LocalNow() and DateTimeZone.LocalNow().
The Date.LocalNow() function returns only the current local date, while the DateTimeZoneLocalNow() function also returns the time.
You can use Date.LocalNow() to create a dynamic column reflecting the current date. For example, you can create a “Today’s date” column in a sales table using the formula “=Date.LocalNow()”.
It will automatically be updated to the current date, so you know that the number in the sales column corresponds to the day’s sales.
Another example is filtering the data in a table to include only records equal to or after the current date and time. This is done using the formula “= Table.SelectRows(TableName, each [DateColumnName] >= Date.LocalNow())”.
Similarly, the ‘DateTimeZone.LocalNow()’ function is useful for creating a dynamic column reflecting both the current date and time. For example, the formula ‘= DateTimeZone.LocalNow()’ can be used to create a column that is updated automatically each time the data in a connection log table is refreshed.
Query editor and current date in the Power Query Date Function
If you don’t want to write formulas, you can use the query editor in Power Query for Excel and Power BI.
To add a column with today’s date to an Excel table, first open the spreadsheet containing the data.
Click on the “Data” tab and select “From Web” or “From File”. Select the file to be imported and click on ‘Open’, then choose the data to be imported and click on ‘Load’.
In the Power Query editor, click on the “Add a column” tab and select “Today’s date”. This column will be added to the query with the current date.
To filter data by today’s date using the query editor, click on the arrow next to the column containing the dates you want to filter.
Choose “Date filters” and click “Is after or equal to”. In the dialogue box that appears, click on ‘Today’s date’ to select the current date. Finally, click OK to apply the filter.
Other date of the day formulas in Power Query Date Function
To add days to the current date with the Power Query Date Function, you can use Date.AddDays(). For example, to add 7 days to the current date, the formula is “= Date.AddDays(Date.LocalNow(), 7)”.
Similarly, you can add months or years using the Date.AddMonths() and Date.AddYears() functions.
The Date.DayOfWeek() function returns the number of the day of the week for the current date. This is done using the formula “= Date.DayOfWeek(Date.LocalNow())”.
Finally, it is possible to return the end date of the month for the current date. The formula to use is “”= Date.EndOfMonth(Date.LocalNow())”.
Once again, these different functions and formulas can be used to create dynamic columns and filters.
Tips and tricks for the Power Query Date Function
It is not always necessary to create a new column containing today’s date. If you are using a data source that already contains such a column, you can retrieve it with Power Query by selecting it and renaming it if necessary.
Another trick is to change the format of the current date if necessary, using the Date.ToText() function. For example, to display the current date in the format “dd/mm/yyyy”, use the formula “= Date.ToText(Date.LocalNow(), “dd/MM/yyyy”)”.
Finally, you can use today’s date as a parameter for dynamic data queries. In the Power Query editor, click on ‘Manage query parameters’ in the ‘Home’ tab. Once you have created the parameter, you can use it in the formulas.
Conclusion: Power Query Date Function are an asset for real-time data analysis
The various Power Query functions and formulas for today’s date are very useful for updating data in real time or for comparing current trends with the past.
But this is just one of the many subtleties of Power Query. To fully master this data transformation tool, you can turn to DataScientest.
Our Power BI training course enables you to learn how to use Microsoft’s Business Intelligence software in just 5 days. You’ll learn about Power Query, the DAX language, Dataviz functionalities and advanced concepts such as DataFlows and incremental updating.
By the end of the course, you’ll be fully conversant with Power BI for analysing data and creating interactive dashboards. You will then be able to sit the Microsoft PL-300 exam to obtain Power BI Analyst Associate certification!
All our training courses are distance learning, and are eligible for funding options. Discover DataScientest!
Now you know all about Power Query’s current date functions. For more information on the same subject, read our dossier on Power BI.
