Power Query Date [With 31 real examples]

In this Power BI Tutorial, we will learn about Power Query date with a few real examples. Here are a few power query date examples:

• Power BI how to use power query current date
• Power BI how to use power query get year from date
• Power BI how to use power query get month from date
• Power Query date minus x days
• Power query date minus 1 day
• Power query date minus 1 month
• Power query date minus 1 year
• Power Query date from text format
• Power query date between
• Power query date from year, month, day
• Power query date add months
• Power query date add years
• Power query date after today
• Power query date as text
• Power query date as number
• Power query date add weeks
• Power query date and time
• Power query date add time
• Power query max date by group
• Power query date in months
• Power query date comparison
• Power BI how to use power query group by month
• Power query date day of week
• Power query date difference years
• Power query date difference in weeks
• Power query date end of month
• Power query date end of year
• Power query date equals today
• Power query date.endof week
• Power query date end of last month
• Power query get date from date time
• Power query date from week number

If you are new to Power query, check out, Power Query Examples [25 Useful examples] and Create table using Power Query in Power BI.

Power query current date

Here will see how to get the current date or today’s date using the Power Query editor in Power BI.

To get the current date in the Power query editor we will use the DateTime.LocalNow().

• In Power Query Editor go to Add column tab -> select the Custom column from the ribbon.
• Then Custom column window will open, and then provide the column name and then write the below formula:

DateTime.LocalNow()

• Now you can see the current date and time in the column. Change the data type of the column to Date.

This is an example of Power BI how to use power query current date.

Read Power BI if date [with 27 real examples]

Power BI how to use power query get the year from date

Here we will see how to get the year from date using the Power Query editor in Power BI.

For this example, we will use the below table, to extract the year from the date column by using the M query function i.e. Date.Year().

To extract the year from the date column, follow the below steps:

• In the Power Query editor, go to the Add column tab-> then select the Custom column from the ribbon.

• Now Custom column window will open, the provide the column name, and in the formula box, writes the below formula to get the year.

Date.Year([Order Date])

Once you click on OK, you can see a new column added to the table with the Year value extracted from the Order date column.

This is an example of Power BI how to use power query get year from date.

Read Power BI IF [with 41 real examples]

Power BI how to use power query get month from the date

Here we will see how to use the Power query to get a month from the date in Power BI.

For this example, we will use the below table to get the month from the Order date column using Date.Month().

To extract the Month from the date column follow the below steps:

• In the Power Query editor, go to Add column tab-> select the Custom column from the ribbon.

• The custom column window will open, provide the name of the column, and then provide the below formula:

Date.Month([Order Date])

Once you click on Ok, you can see the new column added to the table has the month number from the order date column.

This is an example of Power BI how to use power query get month from date.

Read Power BI Pie Chart

Power Query date minus x days

Here we will see how to use the Power Query to minus x days from the date.

For this example, we will use the below table, from the Order date column we will subtract 7 days from each date using Date.Add().

To subtract 7 days from the date present in the order date column, follow the below steps:

• In the Power Query editor, go to the Add column tab-> select the Custom column from the ribbon.

• Then the custom column window will open, provide the name to the column and then write the below formula in the formula box.

Date.AddDays([Order Date], -7)

Once you click on OK, you can see the 7 days are get subtracted from each date present in the order date column.

This is an example of Power Query date minus x days.

Read Power BI Switch

Power query date minus 1 day

Here we will see how to use the Power Query to minus 1 day from the date.

For this example, we will use the below table and we will subtract 1 day from the order date column.

To subtract 1 day from each date present in the date column, follow the below steps:

• In the Power Query editor, go to Add column tab -> select the Custom column from the ribbon.

• Now the custom column window will open, provide the column name and then add the formula to the formula box.

Date.AddDays([Order Date], -1)

Once you click on OK, you can see a new column added to the table, having the date value with order date -1.

This is an example of Power Query date minus 1 days.

Read Power BI Card – How to Use

Power query date minus 1 month

Here we will see how to subtract 1 month from the date column using the Power query editor.

For example, we will use the below table, to subtract 1 month from each date present in the order date column.

To subtract 1 month from an order-date column, follow the below steps:

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.

• The custom column window will open, provide the column name and below formula to minus 1 month from the order date column.

Date.AddMonths([Order Date], -1)

Once you click on Ok, you can see the column is added to the table, having the date value with order-date-1 month.

This is an example of a Power query date minus 1 month

Read Power BI Measure Sum and Subtract Example

Power query date minus 1 year

Here we will see how to subtract 1 year from the date using the Power Query editor in Power BI.

For this example, we will use the below table and subtract 1 year from the date using the Date.AddYears().

To subtract 1 year from the date column, follow the below steps:

• In the Power Query editor, -> go to Add column tab-> select the Custom column from the ribbon.

• Now the custom column window will open, then provide the column name and below formula:

Date.AddYears([Order Date], -1)

Now you can see the column get added to the table and having the value with Order date- 1 year.

This is an example of the Power query date minus 1 year.

Read What is Power Platform

Power Query date from text format

Here we will see how to get dates from text format using the Power query editor.

For example, we will use the below table, in this order date column is in the text format, from this we will retrieve the Date.

To extract the date from the text format column, follow the below steps:

• In the Power Query editor, go to Add column tab-> select the Custom column from the ribbon.

• Now the custom column window will open, provide the column name, then provide the formula in the formula box:

Date.FromText([Order Date])

Next, you see the date extracted from the text column, and then change the data type of the new column from text to Date type.

This is an example of a Power Query date from text format.

Read Remove blank from Power bi slicer

Power query date between

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

For this example, we will use the below table to get the number of days between the order date column and the ship date column using the Duration.Days() in Power query editor.

To get the number of days between two dates, follow the below steps:

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.
• Then the Custom column window will open provide the column name and then provide the below formula:

Duration.Days([Ship date]-[Order Date])

Next, you can see the No. of days column added to the table in Query editor.

This is an example of Power query date between.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power query date from the year, month, day

Here we will see how to get dates from the year, month, and day using the Power query editor.

For example, we will use the below table to get the date from the year, month, and day using the Power query editor.

To get the date from the year, month, day using Power Query Editor, follow the below steps

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.
• Custom column window will open, and then provide the column name and provide the below formula:

Date.FromText([Order Date])

Once you click ok, you can see the column get added to the table with the name Date. Then change the data type to Date from text.

This is an example of Power query date from year, month, day.

Read How to create and use Power BI Bookmarks

Power query date add months

Here we will see how to add months to the date using the Power Query editor in Power BI.

For example, we will use the below month and we will use the ship date column, to add 1 month to each date using Power Query Editor in Power BI.

To add months to date using Power query editor, follow the below steps:

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.
• The custom column window will open, provide the column name and then add the below formula to the formula box. Click on Ok.

Date.AddMonths([Ship date], 1)

Once you click on Ok, you can see the column is added to the table, and change the data type to Date.

This is an example of Power query date add months.

Read difference between calculated column and measure in Power BI

Power query date add years

Here we will see how to add years to the date using the Power Query editor in Power BI Desktop.

For example, we will use the below table to add 1 year to the ship date column using the Power query editor.

To add 1 year to the Date using the Power Query editor, follow the below steps:

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.
• The Custom column window will open, provide the column name and add the below formula to the Formula box.

Date.AddYears([Ship date], 1)

Once you click on OK, you can see the new column is added to the table. Then change the data type to date.

This is an example of Power query date add years.

Read Power bi free vs pro vs premium

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

• Once you click on Ok, you can see a new column added to the table with the date after today. Then change the data type from Any to date

This is an example of Power query date after today.

Read How to use weekday function power bi with example

Power query date as text

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

For this example, we will use the below table and convert date to text using Date.ToText () in Power query editor. That means we will convert the data type of the date column into a text data type.

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

• In Power Query Editor, go to the Add column tab -> select the Custom column from the ribbon.
• The Custom column window will open, provide the column name and then provide the below formula in the Formula box. Click on Ok.

Date.ToText([Order Date])

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 an example of Power query date as text.

Read Contact your admin to enable embed code creation Power bi

Power query date as a number

Here we will see how to convert the date as a numeric value using the Power Query editor in Power BI.

For example, we will use the below table to convert the order date column to a number using Number. From () in Power Query Editor.

To convert the date as a number using Power Query Editor, follow the below steps.

• In the Power Query editor, go to Add column tab -> select the Custom columns from the ribbon.
• Then Custom column window will open, provide the custom column name and then provide the formula using the formula box. Click on OK.

Number.From([Order Date])

Once you click on Ok, you can see the new column added, and the date is converted to a number by using Power Query Editor.

This is an example of Power query date as number.

Read Power BI convert hours to minutes

Power query date add weeks

Here we will see how to add weeks to date using Power Query in Power BI.

For example, we will use the below table, to add weeks to the Order date column, using Date.AddWeeks() in Power Query. As we know weeks are 7 days, so we are adding 7 days to the order date column.

To add weeks to the date column using Power query, follow the below steps:

• In Power Query Editor, go to the Add column tab-> select the Custom column from the ribbon.
• The custom column window will open, provide the column name and formula to the formula box. Then click on Ok

Date.AddWeeks([Order Date],1)

• Once you click on Ok, you can see the column get added to the table, which has the order date + 1 week added value. Then you can change the column data type text to date.

This is an example of Power query date add weeks.

Read Power BI convert yyyymmdd to date

Power query date and time

Here we will see how to convert the column from text to date and time data type using the Power Query editor in Power BI.

We will use the below table and we will change the data type of the Order date column text to date and time data type.

To change the data type of Order column text to Date and time, follow the below steps:

• In Power query editor, select the Order date column-> click on the Data type in the ribbon-> select the Date/Time from the list.

• Now you can see the Order date column get converted from text to date and time.

This is an example of Power query date and time.

Read Get Month Name from Month Number in Power BI

Power query date add time

Here we will see how to add time to date using the Power Query in Power BI.

For example, we have the below table, we will use the two-column from this table one is the Order date column and another is the time column. So, we will add the time column to the date column.

To add time to date using the Power Query editor, follow the below steps:

• In Power Query Editor, select the two columns i.e. Order Date and Time. To select the column Ctrl+ click on the column header.
• Go to the Add column tab-> click on the Merge columns.
• The Merge column window will open, select space as Separator, by default it is None, then provides the New column name.

Now you can see the column get added to the table. That column contains both the date and time. Then you can change the data type to Date/Time from the text.

This is an example of Power query date add time.

Read How to use Power BI sync slicers

Power query max date by group

Here we will see how to get the Max date by group using Power Query in Power BI.

For example, we will use the below table, and we will group the column based on the product and get the Max date for each product.

To get the Max date for each Product group using the Power Query editor, follow the below steps:

• In Power Query Editor, select the product column and then click on the Group By option present in the Home tab ribbon.
• Then Group By window will open, select the Advanced option, select the Product Column for grouping, provide the New column name as Max date, choose the Operation as Max, and next choose the column as Order Date. Click on OK.

Once you click on Ok, you can see the below result, Max date by Product group.

This is an example of Power query max date by group.

Read Power BI Calculated Column Example

Power query date in months

Here we will see how to get months from the date using Power Query Editor in Power BI.

For example, we will use to below table, from the Order date column we will extract the Month using Date.Month() or Date.MonthName() in Power query. Date.Month() will return the Month number, whereas Date.MonthName() will return Month name.

To get the Month from the date using the Power query, follow the below steps:

• In Power Query, go to Add column tab and select the Custom column from the ribbon.
• Now the Custom column window will open, provide the Column name and provide the formula to the formula box. Here two formula is listed, one will return the Month number and another will return the month name.

Date.Month([Order Date])

Date.MonthName([Order Date])

• Once you click on Ok, you can see a new column added to the table with the Month number or Month name.

This is an example of Power query date in months.

Read Power bi conditional column example

Power query date comparison

Here we will see how to perform a date comparison using Power Query.

For example, we will use the below table, to compare the order date column with the current date, if it is less than today, it will return True else return False.

To compare the order date with today follows the below steps:

• In Power Query Editor, go to the Add column tab -> select the Custom column from the ribbon.
• The Custom column window will open, provide the column name and add the below formula to compare the date with the current date. Click on Ok.

if [Order Date] < DateTime.Date(DateTime.LocalNow()) then "True" else "False"

• Once you click on Ok, you can see that a column is added with true and false values. That means if the value is true, then the date is less than today, else greater than today, false.

This is an example of Power query date comparison.

Read How to change data source in Power Bi

Power BI how to use power query group by month

Here we will see how to use the Power Query to group the data by month.

For example, we will use the below table, to calculate the total price for each month using Power Query.

To group the price by month using query editor, follow the below steps:

• In Power Query Editor, select the Month column, then go to the Home tab -> click on Group by icon from the ribbon.
• Group By window will open, select the Basic option, then select the column based on which you want to group, this case Month column.
• Provide the column name, then select the aggregation as Sum and select the Price column. Click on Ok

Once you click on ok, you can see the price is grouped by each month.

This is an example of Power BI how to use power query group by month.

Read Power BI Quick Insights

Power query date day of week

Here we will see how to get the day of the week from the date using Power Query

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

To get the day of the week from the date using the Query Editor in Power BI.

• In Power Query Editor, go to Add column tab -> select the Custom column from the ribbon.
• The custom column window will open, provide the column name and then add the formula to the Formula box. Click on Ok.

Once you click on Ok, you can see the column get added to the table, with the week number based on the date column.

This is an example of a Power query date day of week.

Read Power BI Group By Examples

Power query date difference years

Here we will see how to calculate the year difference between the two dates using the Power query editor.

For example, we will use the below table, to get the year difference between the two dates (Order date and Ship date) using the Power Query editor in Power BI.

To get the date difference years using Power Query, follow the below steps.

• In Power Query Editor, go to Add column tab-> select the Custom column from the ribbon.
• The custom column window will open, provide the column name and add the below formula to get the date difference years.

Date.Year([Ship date])-Date.Year([Order Date])

Once you click on Ok, you can see the column get added to the table with the date difference in years value.

This is an example of Power query date difference years.

Read Power bi shared dataset permissions management

Power query date difference in weeks

Here we will see how to calculate the date difference in weeks using Power Query Editor in Power BI

For example, we will use the below table, and we will use the Order date and Ship date columns. Then I calculated the weeks of the year for both the date column, to get in Add column tab, select the date icon, and Week -> select Week of the year from the option. These two columns i.e. Week of year for Order date and Week of year for a ship date, we will use as reference only.

To calculate the date difference in weeks using the Power Query editor in Power BI, follow the below steps:

• In Power Query Editor, go to the Add column tab -> select the Custom column from the ribbon.
• The custom column window will open, provide the Column name and add the below formula in the Formula box to get the date difference in weeks.

Date.WeekOfYear([Ship date])-Date.WeekOfYear([Order Date])

Once you click on ok, you can see the column get added to the table with the date difference in weeks value.

This is an example of a Power query date difference in weeks.

Read Scheduled Power BI report data refresh

Power query date end of month

Here we will see how to calculate the date end of the month using Power Query.

For example, we will use the below table, we will calculate the date end of the month of each date present in the table by using Date.EndOf Month() in Power Query Editor.

To get the end of the month of each date using Power Query, follow the below steps:

• In Power Query Editor, go to the Add column tab, and select the custom column from the ribbon.
• Then the custom column window will open, provide the column name and add the formula to the formula box. Click on Ok

Date.EndOfMonth([Order Date])

Once you click on Ok, you can see the column added to the table. This column contains the date end of the month.

This is an example of a Power query date end of month.

Read Power BI nan error

Power query date end of year

Here we will see how to get the date of the end of the year using Power Query.

For example, we will use the below table, to get the end of the year of each date in the Order date column using the Date.EndOfYear() in Power Query.

To get the end of the year of each date using Power Query, follow the below steps:

• In Power Query Editor, go to the Add column tab -> select the Custom column from the ribbon.
• The Custom column window will open, provide the column name and then add the below formula in the formula box. Click on OK.

• Once you click on Ok, you can see the column is added to the table, and it contains the end of the year date.

This is an example of Power query date end of year.

Read Power BI report using People Picker Field

Power query date.end of week

Here we will see how to use the Date.EndOfWeek() using Power Query in Power BI.

For example, we will use the below table to get the end-of-week date using Date.EndOfWeek() in Power Query.

To get the end of week date using Power Query, follow the below steps:

• In Power Query, go to the Add column tab -> select the Custom column from the ribbon.
• The custom column window will open, provide the column name, and add the below formula in the formula box. Click on OK.

• Once you click on Ok, you can see the column get added to the table, having the end of week date.

This is an example of Power query date.endof week.

Read Data Labels in Power BI

Power query date equals today

Here we will see how to check the date is equal to today using Power Query in Power BI.

For example, we will use the below table to compare whether the date in the order date column is equal to today’s date, return true, or else false.

To check the date is equal today using Power Query, follow the below steps:

• In Power Query Editor, go to Add column tab -> select the Custom column from the ribbon.
• The Custom column window will open, provide the column name and then add the below formula in the formula box.

Once you click on Ok, you can see the column is added to the table. The column has true and false values based on the condition. As today is 5/5/2022, so it returns true for the last Order date.

This is an example of Power query date equals today.

Read Power bi table visualization

Power query date end of last month

Here we will see how to end the date of last month using Power Query in Power BI.

For example, we will calculate the last month-end date based on the current date using Power Query. So the current date is 5/5/2022, then the end date of last month is 30/04/2022

To get the end date of last month using Power Query, follow the below steps:

• In Power Query Editor, go to Add column tab -> select the Custom column from the ribbon.
• Then provide the column name and add the below formula to the formula box. Then click on Ok.

Once you click on Ok, you can see the end date of the previous month in the column.

This is an example of Power query date end of last month.

Read Subtraction in Power bi using DAX

Power query gets the date from date-time

Here we will see how to get the date from date-time using Power Query Editor in Power BI.

For example, we will use the below table, to get the date from the DateTime present in the Order date column.

To get the date from date time using Power Query, follow the below steps:

• In Power Query Editor, go to the Add column tab -> select Custom column from the ribbon.
• Then custom column window will open, provide the column name and then add the formula to the formula box.

Date.From([Order Date])

Now you can see a column get added to the table, having only date value. Next, you can change the data type from Text to Date.

This is an example of a Power query get date from date time.

Read DAX Filter function

Power query date from week number

Here we will see how to get date from week number using Power Query Editor.

For example, we will use the below table to get the Sunday date from the Week number and Year(ISO format).

To get the Sunday date from the Week number and Year, follow the below steps.

• In Power Query Editor, go to Add column tab -> select Custom column from the ribbon.
• The custom column window will open, provide the column name and then provide the formula to the Formula box. Click on Ok.

Date.AddDays((Date.AddDays(#date([Year],1,1),-4)),(-Date.DayOfWeek(Date.AddDays(#date([Year],1,1),-4)) + [Week]*7))

Once you click on Ok, you can see the column get added to the table. The column contains the Sunday date from week and year.

This is an example of a Power query date from week number.

You may like the following Power BI tutorials:

• Get Current Month Sales Report using Power BI Measure
• Export Power BI reports to PDF
• Get Next Month Sales Report in Power BI
• Export Power BI Reports to Excel
• Microsoft Power BI Alerts

Conclusion

In this Power BI Tutorial, we learned different types of Power Query Date examples. These are:

• power query get current date
• Power BI how to use power query get year from date
• Power BI how to use power query get month from date
• Power Query date minus x days
• Power query date minus 1 day
• Power query date minus 1 month
• Power query date minus 1 year
• Power Query date from text format
• Power query date between
• Power query date from year, month, day
• Power query date add months
• Power query date add years
• Power query date after today
• Power query date as text
• Power query date as number
• Power query date add weeks
• Power query date and time
• Power query date add time
• Power query max date by group
• Power query date in months
• Power query date comparison
• Power BI how to use power query group by month
• Power query date day of week
• Power query date difference years
• Power query date difference in weeks
• Power query date end of month
• Power query date end of year
• Power query date equals today
• Power query date.endof week
• Power query date end of last month
• Power query get date from date time
• Power query date from week number

Bijay Kumar

I am Bijay a Microsoft MVP (8 times – My MVP Profile) in SharePoint and have more than 15 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com