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