Power BI Date Formatting

While accessing data from other data sources it is definite that we will have dates in different formats not as required, so how do we change the format of the dates in the desired format, there is a query for the locale date format in power bi which allows us to change the date format in the local date format.

Date Format in Power BI

In this article, we will show you different ways of Date formatting in Excel using Power BI. Date and Time are sensitive things in MS Excel and Power BI is no different from this as well. In excel, you must have already known there are several ways of playing with Dates and Time format.

Power-BI-Date-Formatting

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Date Formatting (wallstreetmojo.com)

How to Change Date Formatting in Power BI?

You can download the workbook used in this example to practice along with us from the below link.

You can download this Power BI Date Formatting Excel Template here – Power BI Date Formatting Excel Template

To show examples below is the data we are using.

Power BI Date (Excel Data)

Upload the above table to the Power BI file by following the below steps.

  • Open the Power BI file and click on “Enter Data” from the Home tab.
Power BI Date (Enter data)
  • Select the first cell of the table and paste the above-copied table.
Power BI Date (Load)
  • Click on “Load” to upload the data to Power BI; now, we can see this table in the “Data” tab of Power BI.
Power BI Date (Loaded data)
  • As you can see above date is in “MM-DD-YYYY, HH:MM: SS.”
Power BI Date (Date Format)

We can play with these dates by changing this format.

  • Go to the “Data” tab and select the table to view.
Power BI Date (Table View).png
  • Now go to the “Modelling” tab. Under this tab, we have the “Date” formatting section, as this section is hidden.
Power BI Date (modeling)

To activate this section, select the “Date” column from the table.

Power BI Date (Data Type)
  • As you can see above, as soon as we have selected the date column, it has activated. First, in this section, it has the “Data Type” as of now, the data type is “Date / Time” from this drop-down list chooses only “Date.”
Power BI Date (Select date)
  • As soon as you select the data type as “Date” automatically, these changes will be applied to the table, and we can see only “Date” here.
Power BI Date (Date change)

Currently, we have a full-day name, full month name along with the date, and followed by a year. This looks lengthy, isn’t it??

  • To change the date format to “DD-MM-YYYY, click on the drop-down list of “Format” tab. We have several listed formatting codes here, choose “dd-mm-yyyy” format from this list.
Power BI Date (Date-Time)

This will change the date to the below format now.

Power BI Date (changed format).png

This seems easy but imagines a situation where you need to have time separately, date separately, month separately, and day separately.

In such cases, we don’t have time here, so have custom columns we need to open the “Power QueryPower QueryPower Query in Excel is a case-sensitive tool that helps the users search data sources, associate with data sources, and then shape the database according to their requirement. The users can even share their findings and create multiple reports using more query tools.read more” editor.

  • Under the HOME tab, click on “Edit Queries.”
Edit Queries

This will open the “Power BI QueryPower BI QueryMortgage Recast is the process of recalculating the loan repayment schedule when the borrower repays a large amount on account of mortgage principal.read more” editor.

Power Query
  • In the power query editor window, choose the “Add Column” tab; under this tab, we have a huge list of date formats.
Add Column
  • Click on the drop-down list of “Date” and choose “Date only.”
Date Only

Now look at the data table; it has a new column as “Date,” which contains only “date” values from the date & time column.

Date.1

Similarly, after the “Date Only” option, we have options of “Year, Month, Quarter, Week, and Day.” Under each of these, we have several other options as well. Below is the list of these subcategories under this.

Year elements

Based on the selection you make, it will add a new column to the data table without replacing the old column of data. Similarly, using the “Time” option, we can extract the “Time Only” value to the new column.

Time

Once the changes are applied, click on “Close and Apply” under the Home tab.

Close & Apply

This will again upload the data to the “Power BI” file with applied changes. Now we can see new columns under the “Data” tab.

Power BI Date (Changed table)

Note: Power BI Date Formatting file can also be downloaded from the link below, and the final output can be viewed.

You can download this Power BI Date Formatting Template here – Power BI Date Formatting Template

Things to Remember Here

  • The date format in Power BI is similar to the one in MS Excel.
  • We have pre-defined date formats in Power BI.
  • By default, if the system date format in the US, then the same date format will be applied to Power BI as well.

Guide to Power BI Date Formatting. Here we discuss the different ways to change the date format in Power BI with an example and downloadable template. You may learn more about Power BI from the following articles –

  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>