Change Date Formats Using The Power Query Editor

In this tutorial, we’re going to go over a quick solution on how to change date formats using the power query editor in LuckyTemplates. You may watch the full video of this tutorial at the bottom of this blog.

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

Table of Contents

• Inconsistent Date Formats In The Data
• Errors In The Dates
• Changing The Date Format
• Conclusion

Inconsistent Date Formats In The Data

The issue with this data is that the dates are in different 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 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.

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

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.

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

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.

Custom Conditional Formatting Techniques In LuckyTemplates
Year To Date Sales For LuckyTemplates Custom Calendar Tables
LuckyTemplates 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 LuckyTemplates.

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,