In this article, we will show you, How to Format Dates in Power BI with example. Using these formatting options, you can extract Year, Month, Day, Day Number, Day name, Month Start and End etc.
How to Format Dates in Power BI
To demonstrate these date formats, we are going to use the SQL table that we imported in Enter Data article.
In order to format dates in any table, please click Edit Queries option under the Home tab.
Clicking Edit Queries option will open a new window called Power Query Editor.
From the below screenshot you can see the HireDate column. To demonstrate the formatting options, we are going to use this column.
There are couple of ways to format dates in power bi
First approach to Format Dates in Power BI
Please select the Date Column and right-click on it will open the context menu. Select the Transform option and select the formatting option. For now, we are selecting the Year. It means, this property will extract and display the Years from Hire date
From the below screenshot you can see, the date and Time in Hire date column is replaced by the Extracted Year
Second approach to Format Dates in Power BI
First, go to Transform Tab. Under this tab, you can see the Date and Time options. You can use these drop down list to format the dates.
clicking the down arrow beside the Date will show you the drop down list of available date formats. For the demo purpose we are selecting Days in Month
From the below screenshot you can see, the date and Time in Hire date column is replaced by the total number of days in a month
Third approach to Format Dates in Power BI
Above specified approaches are replacing the existing column with the formatted date but this approach will add extra column for formmated date.
First, go to Add Column Tab. Under this tab, you can see the Date, Time, and Duration options. You can use these drop down list to format the date and Time.
Let me select Date Only option
Now you can see the new column (extra column) displaying the date from Hire date
Let me Select Time Only
Now you can see the new column displaying the Time from Hire date
Let me select the name of the day
Now you can see the extra column displaying the day name. Next, hit Close & Apply option under the Home tab to apply these changes.
Please wait until the changed are applied
Now you can see the Date, Day Name, and Time columns under the SQL Employee table
