I was teaching a course on Power Query yesterday in which we imported a text file. Almost immediately, some of my users pointed out that their dates weren’t importing correctly, and we had to cover how to fix date errors right away.
And to underscore the importance of this… this morning I woke up to comments on one of my previous blog posts with similar issues. I figured it’s time to cover the easy way to fix date errors.
The Symptom
My data came from a text file, and was shown in the following format:
As you can see, the data is set up in the Month/Day/Year format. The issue for the user is that their system is set to a different format… in the case of the users in my class their default windows settings were set to Day/Month/Year, which is the Canadian date format. When they tried to convert the data from an “any” data type to a date, it messed it up.
The problem comes in to play because many systems export into a MDY format as they were programmed using US date standards. But with our operating system set to a different format, it tries to interpret dates in the standard set there. So when importing a text file, it looks at 1/12/2000 and interprets it as Dec 1, 2000, not Jan 12, 2000.
Then it hits a date like 1/13/2000. Because there is no 13th month, it returns an error.
One of the class attendees brought up an interesting point as I was explaining this… “I thought that dates were just a format on top of a serial number? So how can it get it wrong”. He was absolutely correct. But in this case we are importing data from another source into Excel (via Power Query)… Excel (Power Query) is trying to determine what that date serial number is based on the system settings. That’s where the issue hits us.
How to fix date errors
At first, you might be tempted to flip the date format in your Windows settings, but that won’t actually help you in the long run. In fact, in the worst case it may fix the issue for the current import, and blow apart other solutions that you’ve built. So that’s really not a practical solution. What we need is a way to tell Power Query what the date settings are for THIS data source. Fortunately, there is a way to override the date format. In truth, this doesn’t so much fix date errors, but rather prevents them from occurring in the first place.
What we do is select the column with our dates in it then:
- Right click the column
- Choose Change Type –> Using Locale
(Yeah, I know… this is hardly a term that Excel users are familiar with, but it allows you to force a different regional setting on the data source.)
You’ll then be prompted with a new dialog where you’ll choose the date, then the Locale you want to use to read it:
The key here is to recognize WHICH locale your data format is emulating. There are hundreds of countries in this listing. My guess is that you’re probably going to pick either your own or English (United States) most of the time. In truth, when working with dates, the country is actually not the important part. The important part is that you pick a country where the MDY or DMY format is consistent with your data source.
Future Proofing
I’ve been fortunate enough to have to deal with this issue very little in my career. I generally leave my system in a US English configuration and most of my imports follow the US date standard, so no issue. But I recognize this as a huge issue for Europeans, as well as any company that conducts business in multiple countries. In both cases this issue comes up over an over again.
There are two great things about using the “Change Type With Locale” feature:
The first is that it avoids relying on an implicit shortcut, explicitly declaring the source data format. This is REALLY handy for future proofing. In Canada, we typically end up with some users in the organization using Canadian standards and some who use US standards (we are a very confused country sometimes.) By specifically declaring the data type, I know that this solution will continue to work even when I send it to someone who uses a different date standard on their PC. Why? Because it’s now defined for the DATA, not the SYSTEM.
The second great thing is that this is a DATA SOURCE SPECIFIC feature. I can set a different format for each data source used in my solution, allowing me to combine several data sets from different countries and still get it consistent.
Drawbacks/Improvements
One thing that I struggled with is this. Back in the old text import tool for Excel, we had a nice feature that looked like this when we were setting data types:
This was fantastic, as I didn’t need to try and figure out which region the data came from, I simply chose the date format that I could see. While it’s great that we can now exhibit some national pride by choosing our country, that doesn’t always help. In the case of Canada, I’d bet that if I asked 5 different people what our official data format is, I’d get 5 different answers.
It would be SO handy if the Power Query team would add some indicator at the end of these options to indicate what the format is. That would be such an easy change to make here, and SOOOOO useful. I honestly don’t think I need to care if my setting is set to English Australia/UK/Ireland/Canada/Belize if it gives me interprets my date in the correct MDY order.
(I actually did email this thought to one of the program managers a few days ago. So hopefully one day we’ll see that change take place.)
17 thoughts on “Fix Date Errors”
Thanks for this and yes, Europeans have to struggle. Wrestling with data since this morning, and always getting the calendar date error.. (deep sigh) (smile and wave) 😉
Ken, thanks for the usefull post.
Not only for dates, but also for financial data; in the Netherlands we use a decimal comma and a period as the thousand seperator. so a lot of the CSV ar actally using ; to work around that , and then teasing out all the formatting errors just took too long.
Until now 🙂
Jos
I have the following date formatted as a text:
160201
160202
160203
etc.
Is there a way to transform that into a date format?
Date.From and/or Date.FromText seem not to work
If the column is called "Dates":
Date.From(
"20"&Text.Start(Text.From([Dates]),2)&"-"&
Text.Range(Text.From([Dates]),2,2)&"-"&
Text.End(Text.From([Dates]),2)
)
Any idea how to convert this textstring into a valid Date format?
"12 mar 2016 10:27:47 fm"
I'm stuck.
I have tried all different locales with no luck.
I suppose I have to convert the month name to a number first but how?
Hi Magnus,
Assuming the column that holds your date is called "Column1":
Add Column --> Add Custom Column
Formula: Date.FromText(Text.Start([Column1],11))
I have a date format of DMMYYYY which power Q turns into some weird dates plus errors.
Date (DDMMYYYY)
1062016
1062016
1062016
1062016
2062016
2062016
2062016
3062016
3062016
3062016
3062016
3062016
6062016
6062016
6062016
6062016
6062016
6062016
7062016
7062016
7062016
7062016
7062016
7062016
7062016
7062016
8062016
8062016
8062016
8062016
9062016
9062016
9062016
9062016
13062016
Changetype to DATE returns these coloumn values
Date (source) Date (changed)
1062016 12-09-4807
1062016 12-09-4807
1062016 12-09-4807
1062016 12-09-4807
2062016 09-08-7545
2062016 09-08-7545
2062016 09-08-7545
Any idea what is going here and how to fix it?
-Michael
Yes, absolutely. The issue is that your windows system dates are not set to display data in a DMMYYYY format, even though that's what your data may be in. As it doesn't recognize it, it's treating those values as date serial numbers, which is why you're getting wonky results.
Instead of using Change Type --> Date, use Change Type --> Using Locale. Then pick a region that uses a DMMYYYY format. Even at that, I wonder about the success factor with your data as your last two dates have a different number of characters. You may need to add a custom column first that turns the value into a 6 digit text: =Text.End("0"&Text.From([Column1]),6)
Hope that helps!
yeah i have been trying to change with locale.. 3-4 different locales no luck and im still new to M so i've been stumbling about googling for some kind of solution.
Will give the custom coloumn a try.
so i managed to add a coloumn with MMYYYY and another coloumn where i put 0 infront of the days so now i have DD in that coloumn.
I tried merging them and then changing to date using locale, now i simply get errors nomatter how i try to change to date.
DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
01062016
Hey Michael,
Can you post this question in the Power Query forum at http://www.excelguru.ca/forums.php ? I think that will be a better place to solve it.
Thanks!
Ken
Unfortunately this didn't work for me. I am in 2016. My data is from the US. I picked the English US locale and even though it show the format that I want it still says it can't format. I sent an email to MS but it is doubtful that they would do anything. I am not sure even where to look any more. I have scanned everywhere and yours was the only one that came up.
Ok, update to the above post. When you are in 2016 and you are in the PowerQuery Editor, you must delete the automatic Changed Type step and do everything manually. Pain in the 'you know what' but at least it worked. Don't know how you can do this step in advance though. Please tell the other people that couldn't resolve the issue as I couldn't reply to them.
Hi Bernice,
Yes, this is working as expected. The issue you had is that the Changed Type step had already been applied, incorrectly converting the dates. Once you try and change the type then, it's too late.
The workaround is actually to select the previous step, then do the Change Type using Locale. That will insert a new step BEFORE the dates are converted incorrectly. The existing Change Type step will then just convert a date to a date.
Alternately, you can delete the changed type step (as you did).
Hope that helps!
Hi Ken,
My source's date entries looks normal (m/d/yyyy) but I am still getting the error any idea as to why I'm still getting it? In fact I am working on multiple files (xlsx) other files we're successfully imported except for this one.
Excellent post. I just encountered this issue and of course, changed the settings, which did not work, ha!
Hello Ken,
This solved my problem. I had to go to the step before "Changed Type" to apply the locale option and it worked for me.
Initially i used the locale option after the "Changed Type" but it did not work