How to Add Date Column with Today’s in Power BI Power Query

While working for a client, I faced a requirement where the report needed a daily filter that automatically reflects the current date. The client wanted the report users to filter data every day without manually selecting the date each time. To achieve this, I decided to add today’s date as a separate column in the dataset using Power BI Power Query, so it could be used directly in report-level filters and slicers.

This can be done by adding a custom column in Power BI Power Query and using the following formula to generate today’s date dynamically:

Date.From(DateTime.LocalNow())

This formula returns the current date during each dataset refresh, making it ideal for applying daily filters and slicers in Power BI reports.

In this tutorial, you will learn how to add a today’s date column in Power BI Power Query using different approaches. Where I will be covered:

• Add today’s date column in Power BI Power Query
• How to add today’s date column using the Advanced Editor (M code) in Power BI
• Add today’s date with time using Power BI Power Query
• How to format the today’s date column in Power BI Power Query
• How to use today’s date column in existing date calculations

Add Today’s Date Column in Power BI Power Query

For this example, we will use a simple sales dataset that contains order details such as Order ID, Customer Name, Order Date, and Amount.

This dataset does not include today’s date, which is required to apply a dynamic daily filter in the Power BI report.

To add today’s date to the dataset, follow the steps below:

1. Open the Power Automate desktop and load your dataset where you want to add the today’s date column.

2. Then, under the Home tab, click Transform data to open the Power Query editor.

3. In the left pane, select the table where you want to add today’s date. Go to the Add Column tab, click on Custom Column.

4. The Custom Column dialog box will appear. In the New column name, enter Today’s Date, and in the Custom column formula, enter:

Date.From(DateTime.LocalNow())

5. After clicking OK, A new column named Today’s Date will be added to the table.

This way, you can add a today’s date column in Power BI Power Query.

Add Today’s Date Column using the Advanced Editor (M code) in Power BI

In some scenarios, you may prefer to use M code instead of the Power Query user interface, especially when you want more control over the query. Power BI allows you to add today’s date as a new column directly using the Advanced Editor in Power Query.

For this example, I will use the same dataset I uploaded to Power BI. Follow the steps below to add a column for today’s date using M code:

1. First, open Power BI Desktop and click on Home -> Transform data to open the Power Query Editor.
2. In the Power Query Editor, select the table where you want to add today’s date. Then, go to Home -> Advanced Editor.

3. In the Advanced Editor window, add the following line of M code to create a new column with today’s date:

,
    #"New Column" = Table.AddColumn( #"Added Custom","Today Date",each Date.From(DateTime.LocalNow()), type date
)

in
    #"New Column"

4. Click Done to save the changes. A new column named Today Date will be added to the table, displaying the current date.

Finally, click Home -> Close & Apply to load the updated dataset back into Power BI.

Advanced Editor is useful when you want to understand or modify the underlying M code and ensures that the Today’s Date column updates automatically every time the dataset is refreshed.

Add Today’s Date with Time using Power BI Power Query

In the above two examples, we learned how to add a column using two different methods in Power BI Power Query. However, you may notice that in those examples, the time value is not included.

If you want to add today’s date along with the current time, follow the steps below:

1. In the Power Query Editor, select the table where you want to add the date and time column. Go to the Add Column tab and click on Custom Column.

2. In the Custom Column dialog box, enter the New column name as Today DateTime. In the Custom column formula, enter the following formula:

DateTime.LocalNow()

3. Click OK to apply the formula. A new column will be added showing the current date and time for each row in the table.

4. If required, you can change the data type of the column by selecting it and choosing Date/Time from the Transform -> Data Type option.

Finally, click Home Close & Apply to save the changes.

This method ensures that the date and time values are updated automatically whenever the dataset is refreshed in Power BI.

Format the Today’s Date Column in Power BI Power Query

After adding today’s date as a column in Power BI Power Query, you may want to display it in a specific format such as dd/MM/yyyy HH:mm tt (for example, 12/01/2026 10:30 AM). Power Query allows you to format date and time values in different ways, depending on whether you want to keep the column as a Date/Time data type or convert it into text.

Method 1: Convert the Column to Date/Time Data Type

1. Open Power BI Desktop and select Home -> Transform data.
2. In Power Query Editor, select the Today’s Date column.

3. Go to the Transform tab. Click Data Type and choose Date/Time.

This ensures the column contains both date and time values and can be used in calculations and time-based analysis.

Note:

This method does not control the display format. The visual formatting can be adjusted later in the Power BI report view.

Method 2: Format Today’s Date Using Custom M Code (Text Format)

Now I want to extract the format dd/MM/yyyy HH:mm tt, convert the date-time value to text.

To do this, follow the steps below:

1. In Power Query Editor, click Add Column -> Custom Column.

2. Enter the following M code:

DateTime.ToText([Today DateTime], "dd/MM/yyyy HH:mm tt")

3. A new column will be created with today’s date and time formatted as text.

This is the way to format the today’s date column in Power BI Power Query.

How to use Today’s Date Column in Existing Date Calculations in Power BI Power Query

After adding Today’s Date as a column in Power BI Power Query, you can use it in various date calculations to make your reports dynamic and automated.

Our dataset contains the Order Date and Today’s Date columns. Now, I want to use these two columns to calculate the number of days between the order date and today.

Follow the steps below to calculate the days’ difference:

1. Open Power BI Desktop and go to Transform data.
2. In Power Query Editor, select Add Column, click Custom Column.

3. Enter the column name as Days Difference. Then use the following formula:

Duration.Days([Today Date] - [Order Date])

This creates a new column that shows how many days have passed since the order date.

Conclusion

In this tutorial, I covered how to add today’s date using a custom column, how to do the same using the Advanced Editor (M code), and how to add today’s date with time. I also showed how to format the date column for today’s date and how to use it in existing date calculations, such as finding the number of days between two dates.

• Filter Dates in Power BI DAX
• Add Column With a Fixed Value in Power BI
• Check If Text is Date in Power BI Power Query
• Add Column Using Power BI Power Query Editor
• Convert yyyymmdd to Date Format in Power BI