How to Get Today’s Date in Power Query

In Power Query, you can use a custom formula to get today’s date.

I will show you how to use a custom formula in Power Query to get today’s date.

Get Today’s Date in a Custom Reference Column

Let’s say you have the Excel table below showing the due dates of various tasks.

You want to add a reference column to the table containing today’s date.

Here’s how you can do it in Power Query:

  1. Select any cell in the table.
  2. Open the Data tab and click the From Table/Range option on the Get & Transform Data group.

The above step loads the table data into the Power Query Editor.

  1. Open the Add Column tab and click the Custom Column command button on the General group.

The above step opens the Custom Column dialog box.

  1. On the Custom Column dialog box, do the following:
  • Enter the text ‘TodayDate’ on the ‘New column name’ box.
  • Place the cursor after the equal sign on the ‘Custom column formula’ box and enter the following formula:
DateTime.Date(DateTime.LocalNow())

Your Custom Column dialog box should appear like the one below.

  1. Click OK.

The above step adds a custom ‘TodayDate’ column to the table, displaying today’s date in short date format.

When you load the query table back into Excel, the dates in the custom column are converted to the General format.

To reapply a date format to the values, select the ‘TodayDate’ column values, then go to the Home tab. In the Number group, open the Number Format drop-down and choose a suitable date format.

In this example, I chose the short date format.

Explanation of the Formula

= DateTime.Date(DateTime.LocalNow())

The formula gets today’s date without the time.

Here’s how the formula works:

  • DateTime.LocalNow() – This function returns the current date and time based on your computer’s time zone.
  • DateTime.Date(…) – This function extracts only the date portion from the date and time value returned in the above step.

I have shown you how to get today’s date in Power Query. I hope you found the tutorial helpful.

While I’ve shown you how to add today’s date in a custom column, you can also use this formula as part of a larger formula.

For example, if you want to calculate the total number of days between a given date and today’s date, then you can use this formula by subtracting the given date and today’s date.

Other Power Query and Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.