Power Query Add Column Date [15 Examples]

In this Microsoft Power BI tutorial, we will discuss how we can add a date column using the Power Query editor in Power Bi with examples, We will also see how we can display the date difference between two dates. Also, we will cover the Power Query Add Column Date examples:

1. Power query add column with today’s date
2. Power query add column date format
3. Power query add a column with the current year
4. Power query add column month
5. Power query add a column with yesterday’s date
6. Power query add column date to text
7. Power query add column datediff
8. power query add column day of week
9. Power query add a Quarter column
10. Power query add date column from another table
11. Power query add column with a specific date
12. Power query add a column with the previous month
13. Power query add column if date greater than today
14. Power query add the year-to-date column
15. Power query add the column date range

Power query add a column with today’s date

Let us see how we can add a custom column with today’s date using the Power Query editor in Power BI.

In this example we are going to use the employee’s table data, I’ll add the custom column and display the current date using the Power query editor.

• Initially, Load the table data into the Power BI desktop, Under the Home option click on the Transform data option.
• In the Power Query editor, select Add column -> Custom column option.
• In the  Custom Column Formula box, enter the custom column name and apply the below-mentioned formula:
• Once the formula has been added to the formula box, click on the Ok button.

=(DateTime.LocalNow())

• By default it will be in Any data type, we can change the data type to date type.
• The screenshot below shows that the custom column has been added with today’s date value.
• Click on the Close and Apply option from the ribbon to reflect the changes on the Power BI desktop.

= Table.AddColumn(#"Changed Type", "Custom", each (DateTime.LocalNow()))

This is how to add a custom column with today’s date using the Power Query editor in Power BI.

Read: How to Merge Column in Power BI

Power query add column date format

Here we will see how we can add the column to change the date format using the power query editor in Power BI,

In this example, we are going to format the date of Joining column presented in the employee’s table data.

• Open the Power BI desktop and load the table data into it, and select the Home -> Transform data option.
• Now select the date column that you want to format, In the Power Query editor, select Add column -> Custom column option.
• Now, enter the New column Name, In the  Custom Column Formula box apply the below-mentioned formula:
• Once the formula has been added to the formula box, click on the Ok button.

Date.ToText(DateTime.Date([Date of Joining]),"YYYY-MM-DD")

• The screenshot below represents the formatted date in the newly added custom column.
• Click on the Close and Apply option to save your changes.

= Table.AddColumn(#"Renamed Columns", "Date Formatted", each Date.ToText(DateTime.Date([Date]),"YYYY-MM-DD"))

This is how to add the column to change the date format using the power query editor in Power BI.

Read Power BI Slicer Multiple Columns

Power query add a column with the current year

Let us see how we can add the column with the current year using the power query editor in Power BI,

• Load the table data into the Power BI desktop, and click on the Home ->Transform data option.
• In the Power Query editor, select Add column -> Custom column option.
• Now, enter the New column Name, In the  Custom Column Formula box apply the below-mentioned formula:
• Once the formula has been added to the formula box, click on the Ok button.

=Date.Year(DateTime.LocalNow())

• The screenshot below indicates that the custom column has been added with the expected current year value (ie 2022).
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

= Table.AddColumn(#"Added Custom", " Current year", each Date.Year(DateTime.LocalNow()))

This is how to add the column with the current year using the power query editor in Power BI.

Power query add column month

Let us see how we can add the column with the current month using the power query editor in Power BI,

• Load the table data into the Power BI desktop, In the ribbon, under the Home option click on the Transform data option.
• Once the data has been loaded, select the date column for which you want to format it.
• Now, enter the New column Name, In the  Custom Column Formula box apply the below-mentioned formula:
• Once the formula has been added to the formula box, click on the Ok button.

=Date.MonthName(DateTime.LocalNow())

• The screenshot below shows that the custom column has been added with the expected current month value (ie October).
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

This is how to add the column with the current month using the power query editor in Power BI.

Read How to duplicate multiple columns using Power Query

Power query add a column with yesterday’s date

Let us see how we can add the column to display yesterday’s date using the power query editor in Power BI,

For example, today’s date is 25-10-2022, whereas the custom column should display yesterday’s date which is (24-10-2022) using the query editor in Power BI.

• Open the Power BI desktop and load the table data into it, under the Home option click on the Transform data option.
• Once the data has been loaded, select the date column for which you want to format it.
• Now, enter the New column Name, In the  Custom Column Formula box apply the below-mentioned formula:
• Once the formula has been added to the formula box, click on the Ok button.

= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1)

• The screenshot below shows that the custom column has been added with the expected yesterday’s date value (ie 24-10-2022).
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

= Table.AddColumn(#"Added Custom", "Yesterday's Date", each Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1))

This is how to add the column to display yesterday’s date using the power query editor in Power BI.

Power query add column date to text

Here we will see how to convert date to text using the Power query editor in Power Bi.

For this example, we will use the below table and convert the date to text using Date. text () in the Power query editor, which converts the data type from date to text data type

To convert the date as text using the Power Query editor, follow the below steps:

• In Power Query Editor, under the Add column tab -> select the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

Date.ToText([Date of Joining])

• Once you click on OK, you can see the new column is added to the table, and the value is converted to text type.

This is how to convert date to text using the Power query editor in Power Bi.

Read How to add an empty column in Power BI

Power query add column datediff

Here we will see how to calculate the difference between two dates to calculate the days count using the Power Query editor in Power BI.

For this example, we will use the employee’s table data to get the number of working days between the last date and date of joining column using the Duration.Days() in Power query editor.

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

= Duration.Days([Last Date]-[Date of Joining])

• In the below screenshot, you can see the new column is added to the table and displayed the day’s count value as below:

= Table.AddColumn(#"Removed Columns1", "Datediff days count", each Duration.Days([Last Date]-[Date of Joining]))

This is how to calculate the difference between two dates to calculate the days count using the Power Query editor in Power Bi.

Power query add column day of week

Let us see how to get the day of the week from the date using Power Query editor in Power BI,

We will get the day of the week from the date using Date.DayOfWeek() function in Power Query.

Follow the below steps to get the day of the week from the date using the Power Query Editor in Power BI.

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

= Date.DayOfWeekName([Date of Joining])

• In the below screenshot, you can see the new column is added to the table and displayed the day of the week as below:

= Table.AddColumn(#"Added Custom2", "Day Week Name", each Date.DayOfWeekName([Date of Joining]))

This is how to get the day of the week from the date using the Power Query editor in Power BI.

Power query add a Quarter column

Here we will see how to calculate and display the quarter value using the Power Query editor in Power BI.

For example, Quarterly returns the value as a number from 1 (January – March) to 4 (October – December).

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

= Date.QuarterOfYear(DateTime.LocalNow())

• In the below screenshot, you can see the new column is added to the table and displayed the Quarter value of the year based on the month as below:

= Table.AddColumn(#"Removed Columns", "Quarter column", each Date.QuarterOfYear(DateTime.LocalNow()))

This is how to calculate and display the quarter value using the Power Query editor in Power BI.

Read How to add a column with a dropdown list in Power Query

Power query add date column from another table

• Power Query is not supported to add a new custom date column from another table. We can only add a new calculated column, using the Power BI DAX function.
• No, it is not possible to add a new custom date column from another table using the Power Query editor in Power BI.

Power query add column with a specific date

Let us see how to add the column with a specific date using the Power Query editor in Power BI.

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

 = Date.FromText("12/11/2022")

• In the below screenshot, you can see the new column is added to the table and displayed mentioned date below:

= Table.AddColumn(#"Added Custom", "Date", each Date.FromText("12/11/2022"))

This is how to add the column with a specific date using the Power Query editor in Power BI.

Power query add a column with the previous month

Let us see how we can add a custom column with the previous month’s value using the power query editor in Power BI,

• Load the table data into the Power BI desktop, In the ribbon, under the Home option click on the Transform data option.
• In the Power Query editor, enter the New column Name, In the  Custom Column Formula box apply the below-mentioned formula:
• Once the formula has been added to the formula box, click on the Ok button.

Date.MonthName(Date.AddMonths(DateTime.FixedLocalNow(), -1))

• The screenshot below shows that the custom column has been added with the expected previous month’s value (ie September) where the current month’s value is October.
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

This is how to add a custom column with the previous month’s value using the power query editor in Power BI.

Read How to add a column with the same value in Power BI

Power query add column if date greater than today

Let us see how we can add column if the date is greater than today’s date using the power query editor in Power BI,

In this example, we will use the last date column from the employee table data to check if the date is greater than today’s date. If the condition matches it should display true else false.

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

if[Last Date] > DateTime.LocalNow() then "True" else "False"

• The screenshot below shows that the custom column has been added with the expected result value based on the condition it displays True / False, where today’s date is (27/10/2022).
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

= Table.AddColumn(#"Changed Type1", "Date", each if[Last Date] > DateTime.LocalNow() then "True" else "False")

This is how to add a column if the date is greater than today’s date using the power query editor in Power Bi.

Power query add the year-to-date column

Let us see how we can add the custom year-to-date column using the power query editor in Power Bi,

In this example, it adds 4 years to the date value representing the date as 2022/ 5/14.

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

• The screenshot below shows that the custom column has been added with the expected result value, where we can see the year has been changed based on the value passed.
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

= Table.AddColumn(#"Added Custom1", "Custom.1", each Date.AddYears(#date(2022, 5, 14), 4))

This is how we can add the custom year-to-date column using the power query editor in Power Bi.

Read How to add column with a fixed value in Power BI

Power query add the column date range

Let us see how we can add the column date range using the Power Query editor in Power BI,

• In the Power Query editor, Select Add column tab and choose the Custom column from the ribbon.
• In the Custom column window, enter the new custom column name and then write the below formula in the Formula box. Click on the Ok button.

let 
alldates = {Number.From([Date of Joining])..Number.From([Last Date])},
DateRange =List.Transform(alldates, each Date.From(_)) 
in DateRange

• The screenshot below shows that the custom column has been added with the expected result value.
• The result will be stored in the list, when we click on the list button it redirects to the date range value list.
• Click on the Close and Apply option from the ribbon so that the changes will be reflected on the Power BI desktop.

= Table.AddColumn(#"Added Custom2", "Custom.1", 
each let alldates = {Number.From([Date of Joining])..Number.From([Last Date])},
DateRange =List.Transform(alldates, each Date.From(_)) in DateRange)

This is how to add the column date range using the Power Query editor in Power BI.

This is how to add column dates using Power Query editor in Power Bi, Also covered the below-mentioned topics in this Power Bi tutorial.

• Power query add column with today’s date
• Power query add column date format
• Power query add a column with the current year
• Power query add column month
• Power query add a column with yesterday’s date
• Power query add column date to text
• Power query add column datediff
• power query add column day of week
• Power query add a Quarter column
• Power query add date column from another table
• Power query add column with a specific date
• Power query add a column with the previous month
• Power query add column if date greater than today
• Power query add the year-to-date column
• Power query add the column date range

You may like the following Power Bi tutorials:

• Power BI DAX Filter Table [With Real Examples]
• How To Remove Filter From Power BI DAX
• How to Filter Blank Value in Power BI
• Power Query Add Column
• Power BI DAX Filter If [With Real Examples]
• Power BI Dax Filter [With 15+ Examples]
• How to add column from another table in Power BI
• How to Append Columns in Power Query
• How to Filter Power BI Dax Based On Condition
• How to merge columns in Power Query