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())
add column with today's date using query editor
add column with today’s date using query editor
  • 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()))
add column with today's date using query editor example
add column with today’s date using query editor example

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")
add column and change date format using power query editor
add column and change date format using the power query editor
  • 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"))
Example of Power query add column date format
Example of Power query add a column date format

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())
add a column with the current year using power query editor
add a column with the current year using the power query editor
  • 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()))
Example of Power query add a column with the current year
Example of Power query add a column with the current year

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

See also  How To Remove Filter From Power BI DAX

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())
Power query add column month
Power query add column month
  • 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.
Power query add column month example
how to add a month column in the power query

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)
Power query add a column with yesterday's date
Power query add a column with yesterday’s date
  • 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))
Power query add a column with yesterday's date example
Power query add a column with yesterday’s date example

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

Power query add column date to text
Power query add column date to text

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])
Example of Power query add column date to text
Example of Power query add column date to text
  • Once you click on OK, you can see the new column is added to the table, and the value is converted to text type.
Power query add column date to text example
Power query add column date to text example

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])
Power query add column datediff
Power query add column datediff
  • 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]))
Power query add column datediff example
Power query add column datediff example

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

See also  Power BI Divide Two Columns + 8 Examples

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])
Power query add column day of week
Power query add column day of week
  • 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]))
Power query add column day of week example
Power query add column day of week example

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())
Power query add a Quarter column
Power query add a Quarter column
  • 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()))
Power query add a Quarter column example
Power query add a Quarter column example

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")
Power query add column with a specific date
Power query add column with a specific date
  • 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"))
Power query add column with a specific date example
Power query add column with a specific date example

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))
Power query add a column with the previous month
Power query add a column with the previous month
  • 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.
Power query add a column with the previous month example
Power query add a column with the previous month’s example

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

See also  How to Convert Number to Text in Power BI [With Leading Zeros]

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"
Power query add column if date greater than today
Power query add column if date greater than today
  • 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")
Power query add column if date greater than today example
Power query add column if date greater than today’s example

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.
Power query add the year to date column
Power query add the year-to-date column
  • 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))
Power query add the year to date column example
Power query add the year-to-date column example

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
Power query add the column date range
Power query add the column date range
  • 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)
Power query add the column date range example
Power query add the column date range example

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:

>