Fixing the Date Format Conversion Error in Power BI: A Comprehensive Guide

While working with data you might have often come accross a situation where the DATE columns in your data might be showing as text in Power BI query editor window. Now in ideal world, you can just convert those text into dates with a single click of a button, however sometimes you get a Date Conversion Error. Now today we will talk about scenarios where these can happen and how to resolve them efficiently. Lets go!

Data Used

Click to Download Data

This is a vehicle crash data from USA during the year 2021 & 2022. I have reduced the number of rows in the data for simplicity. We will be working with the Crash Date column to understand various scenarios.

What will you learn ?

In this we will primarily understand resolution to 2 scenarios

1. Scenario #1 - Error occuring because the Date formatting in your data is different from your computer settings or data might have different delimeter in each row i.e. "-" , "/" etc..

2. Scenario #2 - Error occuring because there is a problem in the DATA entry itself i.e. one row might have date entered in DD/MM/YYYY format & another row might have data in MM/DD/YYYY format

While resolving these scenarios, we will see how to use interesting functions in Power Query (M Query) like Date.FromText, Text.Start(), Text.Middle(), Text.Right() & finally using all of these with Try & Otherwise clause. So it is going to be jam packed content on how to use resolve Date Conversion Error in Power BI / Power Query. Hope you are excited to jump right !

Lets Import the Data

Before starting lets import the above csv data into Power BI. After you load the data into Power BI, lets begin with some transformation steps. And the first step in any transformation is to check the Data type of each column. And for todays exercise we are concerned with the data type of column "CRASH DATE"

Date Conversion Error Scenario #1 - Date Formatting Differences

Now in the data transformation window of Power Query, we see that column CRASH DATE is formatted as Text value, so the simplest way to convert that is by clicking on the data format button and selecting Date from the drop down.

But doing this will give you an error, because the date stored here are with different delimeters for example row1 is stored with "-" and row2 is stored with a "/"

However in this scenario, we are assuming that all row of our data is stored as "MM/DD/YYYY" format. And the only issue with the data is the delimeter. So in this case will use a function in M Query which is Date.FromText.

Steps to implement solution

1. Create a new custom column, since we should always avoid tampering with the original raw data column since we might require it at a later stage. I'm going to name this new column as crash date new.

2. Enter below formula in custom formula section. Remember to keep the "MM" in CAPITAL letters, otherwise this will not work properly

Date.FromText([CRASH DATE], [Format = "MM/dd/yyyy"])

3. Now the new column has been successfully created and Power BI has converted this into date, however you still might have to change the text formatting. So click on the formatting button and change this to DATE

Date Conversion Error Scenario #2 - Data Entry Issue

Now in this scenarios, lets use the same data and assume that it has a data entry issue, i.e. few rows are stored in DD/MM/YYYY format (eg. 1/12/2022) and few rows are stored in MM/DD/YYYY format (eg. 12/1/2022). So in this case since the data itself is not stored properly, we will have to disect the Date column and fetch our desired information.

Steps to implement solution

1. Create a new custom column again and lets name this as "CRASH DATE NEW1"

2. In this we will disect the original column. If you are familiar with Excel then you might know that we few functions which can extract characters from an existing string i.e. Left(), Mid() & Right(). Power Query also has a similar function and these are : Text.Start(), Text.Middle() & Text.Right().

So enter below function to first extract each element of our date seperately & then club them together to form a date. Finally use Date.FromText again to convert that string to date.

Date.FromText(Text.Start([CRASH DATE],2) & "-" & Text.Middle([CRASH DATE],3,2) & "-" & Text.End([CRASH DATE],4))

Just to explain above formula, from CRASH DATE column we are first extracting 2 characters from left using Text.Start. Extracting 4th & 5th characters from middle using Text.Middle and then finally extracting last 4 characters using Text.End.

3. Now after doing this you will notice that you have errors in all those rows where the date was entered with a delimeter "/". That is because because Power BI was still not able to understand those values as Dates. So if that happens, we can use another approach by adding an additonal layer of TRY OTHERWISE. This is a function in M Query which is similar to a IFERROR in excel, which will help us in isolating or capturing those rows where errors are coming and then we can use some other function to solve our problem.

So now enter below function to rectify the error values

try 
	Date.FromText(Text.Start([CRASH DATE],2) & "-" & Text.Middle([CRASH DATE],3,2) & "-" & Text.End([CRASH DATE],4))

otherwise 
	Date.FromText(Text.Middle([CRASH DATE],3,2) & "-" &  Text.Start([CRASH DATE],2) & "-" & Text.End([CRASH DATE],4))

And to explain the above formula, we are first trying to construct the date in a DD/MM/YYYY format and if that gives an error then Otherwise clause will try to construct the date in a MM/DD/YYYY format.

4. So after this you can again use the data format button to convert your column into a date column

So this is how you solve the Date Conversion Error in Power BI.

Quick Tip:

In few scenarios, you can use the Try & Otherwise clause in the Date.FromText function directly as well, like below:

try
	Date.FromText([CRASH DATE], [Format = "MM/dd/yyyy"])
otherwise
	Date.FromText([CRASH DATE], [Format = "dd/MM/yyyy"])

So thats it for today, hope this was helpful. Stay tuned for more !!