In Excel, we have TODAY function that returns the system current date. We will look at two different methods to get today/current date in Power Query.
Video Tutorial
Subscribe to YouTube Channel for Exciting Tips & Tricks On Office Applications
Get Access to Your File
Enter your name and email to receive access to the file.
Method 01 (Excel Only)
We will get this done using User Interface (UI) without writing any M code.
- In Excel Workbook enter the TODAY Function in any of the cell or the best way is to create a parameter table.
=TODAY()
- We need to Define Name to the cell “A1” which holding our today date
- Select the cell A1 goto the Name Box
- Rename as to any name, we will call as “TodayDate”
- Select the cell that which we have Define Name as “TodayDate”
- Goto Data Ribbon Tab > From Table/Range
Once you click on From Table/Range the Define Name will be loaded into the Power Query Editor.
Since the date loaded as Date & Time in the Power Query Editor we need to change the Data Type as Date only.
- Select the entire column
- Goto Transform Ribbion Tab > Data Type > Date > Replace Current
The column is changed to Date Data Type & we need to convert this column value as a parameter which can be used anywhere in the Power Query.
- Select the Row [1] > Right Click > Drill Down
We got the Date as parameter and ready to use anywhere inside the Power Query.
Once you are done go to Home Ribbon Tab > Close & Load To… > Only Create Connection
📝 Note: If the Date is deleted from the Excel cell then the query will return error always make sure that the Date is available.
Method 02 (Power Query [Power BI & Excel])
This method we will completely extract the current date with the M code.
Original Formula
= DateTime.Date( DateTime.LocalNow() )
Open the Power Query Editor in Power BI or Excel, copy the formula and paste it into the formula bar of the blank query.
How this works ?
This Power Query function DateTime.LocalNow() will return the current date and time on the system.
We need to extract only the Date from this record. So we will nest it into another function DateTime.Date() which will only return the Date.
We have the Today Date now in the Power Query which can be use anywhere as parameter.
Make sure to give your Query a descriptive name & load as a connection only not loading into model or Spreadsheet.
Was the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
If no, please let us know what to improve.
Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.