Change Date Formats Using The Power Query Editor

by | Power BI

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. You may watch the full video of this tutorial at the bottom of this blog.

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, power query change date format screenshot 1

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

We’re going to change date format 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, power query convert text to date screenshot 1

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

change date formats, power query text to date screenshot 1

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

***** 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

Conclusion

By using this technique we’re able to run a simple solution in the query editor to change text to 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

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.