How to Change the Date Format in Power BI?

When accessing data from other data sources, you will inevitably encounter dates in formats that are not as desired or suited for your reports. Therefore, how can you change the date to your desired format? Power BI Date Format has a query for the locale date format, which allows you to change the date format to the local date format.

You should be aware that there are several ways to manipulate the Power BI Date Format. However, you may have some confusion about changing the date format in Power BI. In this blog, we will demonstrate various methods for changing the date format in Power BI, allowing you to create visually appealing and accurate dashboards.

What is Power BI?

Power BI is a proprietary Data Visualization and Business Intelligence platform from Microsoft Power Platform. It is one of the most used business tools for analyzing and reporting on company data. Power BI seamlessly connects to data, delivering Immersive Visuals, Interactive Reports, and Insights via a variety of built-in software services, apps, and connectors.

Connecting to a number of data source connectors in Power BI allows you to easily import data from the data source and view data stored in databases or data warehouses.

Why You Should Change the Date Format in Power BI

1. Improved Readability: Ensures dates are easy to understand for your audience by aligning with regional or cultural preferences.
2. Accurate Analysis: Prevents confusion during data analysis, especially when collaborating with teams from different regions.
3. Consistent Reporting: Maintains uniformity across dashboards and reports, making them more professional and user-friendly.
4. Better Data Sorting: Enables accurate sorting and filtering of dates for insights, preventing misinterpretation of chronological order.
5. Enhanced User Experience: Tailors the presentation to audience needs, improving overall comprehension and decision-making.

Common Date Formats in Power BI

Power BI supports a variety of date formats to suit different regions and preferences. Here are some commonly used formats:

1. Short Date: Displays dates in a concise format, e.g., MM/DD/YYYY or DD/MM/YYYY.
2. Long Date: Includes the full date with the day and month name, e.g., Monday, December 11, 2024.
3. Year and Month: Focuses on the year and month only, e.g., 2024-12.
4. Month and Day: Shows just the month and day, e.g., December 11.

Custom Formats: You can create formats like YYYY/MM/DD or MMM-YY based on your needs.

How to Change Date Format in Power BI

Before formatting dates in Power BI, ensure that Power BI Desktop is set to your relevant region. To examine and alter your regional settings, go to the Power BI dashboard and select:

File – > Options & Settings –> Options.

There are several ways to format dates in Power BI, and we will provide you with a step-by-step guide on the 3 most effective methods to change your Power BI date format. We’ll utilize the SQL table that we imported in the “Enter Data” article to show various Power BI date types.

When you select the Edit Queries option, a new window called Power Query Editor appears.

The HireDate field can be seen in the screenshot above. This column will be used to demonstrate the Power BI date formatting choices.

• Method 1: Using the Transform Option
• Method 2: Using Date Time options
• Method 3:Using the Add column option

Method 1: – Using the Transform Option in the Column

Please pick the Date Column and open the context menu by right-clicking on it. Select the “Transform” option, followed by the “Formatting” option. For now, we will select the “Year”. This property extracts and shows the Years from the Hire date.

The date and time in the Hire date column have been substituted by the Extracted Year in the example below.

Method 2 – Using Date & Time Options

To begin with, in the Power BI Date Format, navigate to the “Transform” tab. The “Date” and “Time” choices are available under this tab. You can format the dates using these drop-down options.

The date and time in the Hire date column have been adjusted to reflect the total number of days in a month, as shown in the example below.

The date and time in the Hire date column have been changed by the total number of days in a month in the example below.

Method 3 – Using the Add Column Option

The drop-down list of different data formats is displayed when you click the down arrow beside the “Date”. We will use the ‘’Days in Month’’ example for this method. Follow the simple step-by-step instructions below to change the format.

The date and time in the Hire date column have been adjusted to reflect the total number of days in a month, as shown in the example below.

• Step1- In Power BI, select the “Date Only” option. You may now see the new (extra) column displaying the date from the Hire date.
• Step 2- Select “Time Only”. You may now see the new column indicating the Time since the Hire date.
• Step 3- Choose the day’s name. The extra column displaying the day’s name is now visible. Then, under the “Home” tab, select the “Close & Apply” option to save your changes.

• You may now see the new column indicating the Time since the Hire date.
• Choose the day’s name.
• The extra column displaying the day’s name is now visible. Then, under the Home tab, select the Close & Apply option to save your changes.
• Please wait as the changes take effect.

Under the SQL Employee table, you can now view the Formatted Date, Day Name, and Time columns.

How to Deal with Inconsistent Date Formats in Power BI

Inconsistent date formats can cause errors in data analysis and reporting. Here’s how you can resolve them in Power BI:

1. Use Power Query: Load your data into Power Query Editor and use the “Transform” options to detect and unify date formats.
2. Change Data Type: Ensure the column is set to the “Date” data type to automatically standardize the format.
3. Format Dates: Use the “Format” feature in Power BI to apply a consistent format across all records.
4. Custom Transformations: Apply custom transformations to manually adjust irregular formats using Power Query’s M code or advanced options.
5. Error Checking: Check for errors or blank values after transformation to ensure all inconsistencies are addressed.

Conclusion

In this guide, we’ve walked you through practical ways to manage date formatting in Power BI. Whether you’re adjusting regional settings, customizing formats in the Reports View, or using the Query Editor for more granular control, these methods help ensure your date data is displayed accurately and consistently across your reports.

The first step in formatting dates is to confirm that you are using the proper locale for your date settings for your Power BI Date Format. If changing the regional locale settings does not fix your problem, you can format dates in the Power BI reports view.

Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day! Hevo Data offers a faster way to move data from Databases or SaaS applications into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

You can try Hevo for free by signing up for a 14-day free trial. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

FAQ