3 Easy Steps to Change the Date Format in Power BI

MS ‌Power BI provides an easy and comprehensive way for users to import data from other sources and extract relevant insights. Moreover, users can use data from different sources by adding them to their current reports and analytics.

However, there must be a noticeable misalignment of date formats between multiple reports. It’s a common challenge for Power BI users. So, how can you use data with different date formats and a uniform approach for all the dates?

In this post, we will talk about working with different date formats in Microsoft Power BI. More specifically, we will focus on changing date formats, letting you use the desired local formats for customized reports.

Quick Overview of Power BI

Before we jump to date formats in Power BI, let’s quickly understand what this tool is all about. It’s a business intelligence tool that helps with data visualization. So, if you have a Power BI desktop application, you can create interactive reports, extract valuable insights, and integrate with other powerful apps to enhance the applications of this software.

Thanks to Power BI, you can:

• correlate different data sources by using connectors in the Hybridized model
• slice data models into smaller pieces to extract meaningful data and visualization for reporting
• apply AI and machine learning models to extract data to predict future trends for the business
• create and share interactive reports within the organization for collaboration with complete security

Steps to Change Date Format in Power BI

As a user, you can change the Power BI date format by employing some simple steps. In this post, we will look at how to run these steps to convert date format for your applications successfully.

In this post, we will focus on how to change date format to change the date format in Power BI. Here are the steps you need to follow.  

Changing Date Format in Power BI

To change the date format with the transform tool, follow these simple steps.

• Open the Power BI desktop and open a new file. You can also use a previously saved file to transform date formats.
• Go to File and click on Get Data to load the data to your Power Bi desktop file.
• Since you’re modifying date formats, having a date column in your file is crucial.
• Now, go to the field pane and click the column with dates. It should open the column tools where you can modify the date formats.
• Go to the formatting section and look for the desired date format from the dropdown list.

Changing Date Format in SQL File with Power BI

Let’s look at another example where we shall use Microsoft SQL to change the Power BI date format.

1. Ensure the Right Region

Start by confirming that you’re using the correct region for your time and date. Then, you can alter the regional settings in Power Bi Desktop by navigating to ‘Options’ in the ‘Options & Settings’ tab under the ‘File’ menu.

1. Select Edit Queries

Select the ‘Edit Queries’ option under the ‘Home’ tab on the Power BI desktop, and it will open a new window called the Power Query Editor. Your SQL file must have a column with time and date formats to go through this example.

This is a prerequisite step for changing the date format in SQL files. From here on, there are three different ways to change Power BI Date Format.

1. Extract a Specific Date Field

• Using the Transform Tool

The first approach is to use the ‘Transform’ tool. Here’s what you have to do.

• Please select the date column in your SQL file and right-click on it.
• Go to ‘Transform’ and then choose ‘Formatting Options’
• Select any preferred options, say a month, from the given dates.
• Click ‘month’, and it will extract the months from all the cells in the date column.

Once you have selected the month setting, you should see that the date column has been updated with the extracted month of each given date. Remember that the new format will substitute for the old dates.

Using the same method, you can modify the dates to extract Year, Quarter, Week, Day, Hour, Minute, and Second from any given time or date.

• Using the Dropdown List

Another simple way to change date formats in Power BI is to use the Transform tab.

• With the Power Query Editor opened, go to the ‘Transform’ tab in the upper ribbon.
• You should see the ‘Date’ and ‘Time’ dropdown lists here.
• Select the desired column and then pick your preferred dropdown list, i.e., ‘Time’ or ‘Date’.
• For instance, pick the Date dropdown and then navigate to ‘Year’. You can choose ‘Start’, ‘End’, or ‘Year’ to change the format.
• Once you make the desired selection, the selected columns will be updated with the new format.

Some Common Power BI Date Formats

You can format the date as a number or plain text in Power BI. While changing the date formats to your requirements, knowing the predefined Power BI Date formats is also helpful. Here is the list:

• MMM-YY
• YYYYMMDD
• Short month name
• DD/MM/YYYY

Essentials to Know About Power BI Date Formats

While changing the date format in Power BI, it’s also important to remember the following points.

• Power BI has predefined date formats
• Microsoft Excel and Power BI have similar date formats.
• Power BI uses the default date format according to the regional settings.
• When you create a relationship in Date columns, they may need to work in an Analysis service, as you might expect.

Why Date Format Can Change When Power BI file is Published

In some cases, the Power BI file may experience changes in date format. Typically, it happens due to the change in the region. So, the date format follows the default language setting for each File. That’s why it’s a good idea to recheck the date formats to avoid confusion during the reporting phase.

Conclusion

Power BI is a comprehensive tool that can ease your life while reporting and creating visualization and reports for your data. Since date and timings are critical elements of any report, it’s essential to understand how to manipulate them to make your reports more engaging and insightful.