Change Date Formats Using The Power Query Editor

No comments

In this tutorial, we’re going to go over a quick solution on how to change date formats using the power query editor in Power BI.

This is from a recent Enterprise DNA member-only event which featured the production data of an oil and gas company from a certain area in the United States.

Inconsistent Date Formats In The Data

The issue with this data is that the dates are in different formats.

change date formats

This data was downloaded online so the dates are currently in U.S. format, which is Month/Day/Year.

We’re going to change it into the New Zealand or UK format for dates, which is Day/Month/Year.

There are a few ways that we could do this, but one of the simplest ways is to use the query editor. 

Errors In The Dates

We need to import the CSV file of the production data in the query editor.

change date formats

The column for the date is currently in a text format.

change date formats

If we try to change it into a date format, it’s going to say that we need to change the column type.

And if we select the Add new step option, we’re going to get errors.

So, there’s currently something wrong with the data.

Changing The Date Format

Here’s what we need to do.

First, we’re going to initially split the column by delimiter.

We’re going to split them using the forward slash. We need to split them at each occurrence.

After the column has split, we’re going to change the name of each column. We’re going to name the three columns Month, Day, and Year, respectively.

change date formats

Then, we’re going to rearrange them into the format we want. So we need to put the Day column first.

change date formats

Next, we need to highlight the three columns and right click to select the Merge Columns option.

Then, we can select a custom delimiter. For this example, we’ll continue using the forward slash like before and change the column name to Production Dates.

The new column will originally be in a text format. But if we try to change it into a date format, we’ll no longer have the same error as before.

change date formats

Conclusion

By using this technique we’re able to run a simple solution in the query editor to change date formats in Power BI.

We didn’t have to write codes or formulas. We were able to get the output we wanted through simple points and clicks. Hopefully, you’ll be able to apply the same technique when doing your own analysis.

All the best,

Sam

Membership Banne
Center of Excellence

***** Related Links *****
Custom Conditional Formatting Techniques In Power BI
Year To Date Sales For Power BI Custom Calendar Tables
Power BI Query Parameters: Optimizing Tables

***** Related Course Modules *****
Best Practices & Feature Reviews
Ultimate Beginners Guide to Power BI
Advanced Data Transformations & Modeling

***** Related Support Forum Posts *****
Custom Date Formats For Power BI
Changing The Date Format In Power BI – How Does It Work?
Dates Format Error DD/MM/YY Vs. MM/DD/YY – Power BI
For more date format queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.