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.

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.

To show examples below is the data we are using.

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.

• Select the first cell of the table and paste the above-copied table.

• Click on “Load” to upload the data to Power BI; now, we can see this table in the “Data” tab of Power BI.

• As you can see above date is in “MM-DD-YYYY, HH:MM: SS.”

We can play with these dates by changing this format.

• Go to the “Data” tab and select the table to view.

• Now go to the “Modelling” tab. Under this tab, we have the “Date” formatting section, as this section is hidden.

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

• 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.”

• 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.

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.

This will change the date to the below format now.

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.”

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.

• In the power query editor window, choose the “Add Column” tab; under this tab, we have a huge list of date formats.

• Click on the drop-down list of “Date” and choose “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.

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.

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.

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

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

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

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.

Recommended Articles

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 –

• Parameter in Power BI
• Prepare Reports in Power BI
• Custom Visuals in Power BI
• Power User in Excel
• Power BI Themes