How to change Power BI Date Format?: 3 Easy Approaches

on BI Tool, Business Intelligence, Data Visualization, ETL Tutorials • March 25th, 2022 • Write for Hevo

When Accessing Data from other Data Sources, It is Inevitable that we will encounter dates in formats that are not as desired; therefore, how do we change the format of the dates to the desired format. Power BI Date Format has a query for the locale date format that allows us to change the date format in the local date format.

In this blog, we will demonstrate various methods for formatting dates in Microsoft Excel using Power BI. Date and time are important items in MS Excel, and Power BI is no exception. You must be aware that there are numerous ways to manipulate Power BI Date Format.

Table of Contents

What is Power BI?

Power BI Date Format - PowerBI Application
Image Source

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.

Microsoft Power BI is available on desktop, mobile, and cloud platforms, enabling your teams to collect, manage, and analyze data from any place. Data may be imported into Power BI from a variety of sources, including Excel, CSV, SQL Server, MySQL databases, PDF, Access, XML, JSON, and many others.

Understanding Microsoft Power BI: A Comprehensive Guide is a good place to start if you want to learn more about dashboards, reports, views, and all about Power BI.

Key Features of Power BI

The following are some of the most notable features of Power BI.

  • Hybridization: Power BI communicates with a variety of connectors, allowing users to connect to a variety of data sources.
  • Modeling Point of View: Users of Power BI can divide and slice big data models into smaller chunks, generate unique visualizations, and pick several possibilities.
  • AI Assistance: AI may be used by Power BI Data Analytics users to prepare data, build Machine Learning models, and swiftly reveal insights from structured and unstructured data.
  • Important Things to Keep in Mind: Power BI makes it easier to create data subsets and apply Data Analytics to that data.
  • Report Distribution: Users can quickly share their reports with other members of their company without having to worry about data security.

Simplify the Power BI Visualization Process with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ Data Sources (including 40+ Free Sources) such as Power BI to a Data Warehouse/Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. In addition to the 100+ data sources, Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector.

Get Started with Hevo for Free

Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Connectors: Hevo supports 100+ Integrations to SaaS platforms, Files, Databases, BI tools such as Tableau, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; Databricks (Connector Live Soon!); MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.  
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources) such as Tableau that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

How Power BI Format Dates?

Before you format dates in Power BI, make sure that Power BI Desktop is set to the 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 describe the majority of them. We’ll utilize the SQL table that we imported in the enter data article to show various Power BI date types.

Power BI Date Format - Hire Data
Image Source

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.

1) The First Approach to Power BI Date Format

Power BI Date Format - First Approach
Image Source

Please pick the Date Column and open the context menu by right-clicking on it. Choose the Transform option, then the Formatting option. For the time being, 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.

Power BI Date Format - First Approach Hire Data
Image Source

2) The Second Approach to Power BI Format

To begin with, 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.

Power BI Date Format - Second Approach
Image Source

The drop-down list of different data formats is displayed when you click the down arrow beside the Date. We’re going to use Days in Month as an example.

Power BI Date Format - Seggregating via Month
Image Source

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.

Power BI Date Format - Month Hire data
Image Source

3) The Third Approach to Power BI Format

The drop-down list of different data formats is displayed when you click the down arrow beside the Date. We’re going to use the Days in Month example. Follow a simple step-by-step instruction below!

Power BI Date Format -Data
Image Source
  • 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.
  • In Power BI, let me select the Date Only option.
  • You may now see the new (extra) column displaying the date from the Hire date.
  • Select Time Only
Power BI Date Format - Time
Image Source
  • 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.
Power BI Date Format - Query Changes
Image Source

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

Things to Remember in Power BI Date Format

Here are a few things you should “know” before diving into Power BI Date:

  • The date format is comparable to that of Microsoft Excel.
  • In Power BI, we have predefined data formats.
  • By default, if the system date format in the United States is used, Power BI will use the same date format.

Limitations of Power BI Date Format

The Limitations of Power BI Format Date are mentioned below:

  • Relationships created on the DateTime columns may not operate as expected when using Analysis Services data sources.
  • Date and Time data types are Power BI formatting elements implemented on top of Analysis Services, therefore any model-dependent objects will still look as DateTime to Analysis Services.
  • As a result, even if a user selects Date from the Modeling tab for such columns, they do not register as the same date since Analysis Services still considers the time element of the data.

Conclusion

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. Finally, if you need to extract sections of dates or if your date formats are inconsistent, you can use the Power BI Query editor to gain greater fine-grained control over date forms.

Hevo Data is a No-Code that supports data ingestion from multiple sources be it your frequently used databases or SaaS applications like MySQL, PostgreSQL, Salesforce, Mailchimp, Asana, Trello, Zendesk, and other 100+ Data Sources. Hevo migrates your data to a secure central repository like a Data Warehouse in minutes with just a few simple clicks.

Using Hevo is simple, and you can set up a Data Pipeline in minutes without worrying about any errors or maintenance aspects. Hevo also supports advanced data transformation and workflow features to mold your data into any form before loading it to the target database.

Visit our Website to Explore Hevo

Hevo lets you migrate your data from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt, within minutes to be analyzed in Power BI.

Why not try Hevo and the action for yourself? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check out our pricing plans to choose the best-matched plan for your business needs. 

Share your knowledge on Power BI Date Format in the comment section below! We would love to hear your thoughts.

No-code Data Pipeline for Power BI