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.
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 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 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 “Power Query” editor.
- Under the HOME tab click on “Edit Queries”.
This will open the “Power BI Query” 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
- 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 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
