WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn Power BI » Power BI Date Formatting

Power BI Date Formatting

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

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??

Popular Course in this category
Sale
Power BI Training Certification
4.5 (521 ratings)
6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access
View Course
  • 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 Query” editor.

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

Edit Queries

This will open the “Power BI Query” 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.

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
7 Shares
Share
Tweet
Share
Power BI Training Certification
  • 6 Online Courses
  • 29+ Hours
  • Verifiable Certificate of Completion
  • Lifetime Access
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI Date Formatting Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI Date Formatting Excel Template

Special Offer - Power BI Training Certification (22+ Hours of Training) View More