Date Conversion is one of the simplest conversions in Power Query, however depends on locale on the system that you are working with Date Conversion might return different result. In this post I’ll show you an example of issue with date conversion and how to resolve it with Locale. In this post you’ll learn that Power Query date conversion is dependent on the system that this conversion happens on, and can be fixed to a specific format. If you want to learn more about Power BI; Read Power BI online book; from Rookie to Rock Star.

Video

Prerequisite

The sample data set for this post is here: book1

Different Formats of Date

Most of the countries uses YMD format, however some of them use MDY or DMY more frequently. This wikipedia page explains different formats of date in each country. Data set below have different formats in it;

In above table, first two columns (Date1, and Date2) are in YYYY-MM-DD and YYYY/MM/DD format, which is the most common format of date. Date3 is MM-DD-YYYY format (very common in USA and some other countries), and Date4 is DD-MM-YYYY format, which is mostly common in New Zealand, Australia, and some other countries. Now let’s see what happens if we load this data into Power BI.

Automatic Type Conversion

Power BI leverages automatic data type conversion. This automatic action sometimes is useful, sometimes not! If you open a Power BI Desktop file and get data from specified data set. in Query Editor you will see the data types converted automatically at some level. Here is the data loaded into Query Editor window with automatic data type conversion;

You can see the Changed Type step that applied automatically and converted first three columns in the data set to data type of Date, and left the Date4 as data type Text. Automatic Date type conversion understand characters like / or -, and apply conversion correctly in both cases (Date1, and Date2)

* If you are running this example on your machine you might see different result, because Power Query uses the locale of machine to do data type conversion. Locale of my machine is US format, so it understand format of USA and convert it automatically. If you have different locale the conversion might result differently. We will go through that in a second.

If the automatic data type conversion is not something you want, you can remove that step simply. Or if you want to disable the automatic type conversion, Go to File, Options and Settings, then Options.

In the Options window, under Current File, Data Load section. you can enable or disable the automatic data type detection if you wish to. (In this example I’ll keep it enabled)

You can also check the Locale of your current machine in the Regional Settings section of Options Window;

Date Conversion Issue

Date4 in the data set isn’t converted properly, and that’s because the Locale of current system (my machine) is English (United States). I can change the locale to English (New Zealand) in the Options window, and Refresh the data, but this will corrupt the existing Date Conversion of Date3 column. If I try to change the data type of Date4 column myself, the result will not be correct and I’ll see some errors;

You can see that conversion didn’t happened correctly, and also it returned Error in some cells, because my machine is expecting MM/DD/YYYY, but the date format in the column is DD/MM/YYYY which is not that format. So it can convert first 12 records, because it places the day as month in the result! and the rest it can’t because there is no month 13 or more.

Date Conversion Using Locale

Fortunately you can do date conversion using specific Locale. All you need to do is to go through right click and data type conversion using Locale;

In the Change Type with Locale, choose the Data Type to be Date (Normally Locale is for Date, Time, and Numbers). and then set Locale to be English (New Zealand). You can also see some sample input values for this locale there.

With this simple change, you can now see the Date4 column converted correctly. You still see that in MM/DD/YYYY format in Query Editor window, and that’s because my machine’s date format is this. The actual column data type is Date however, which is the correct format to work with.

A Look at M Script

A look at formula bar and Power Query script shows that the locale used for this conversion is “en-NZ”, while other data type conversion are not using that.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Reza\SkyDrive\Blog\DateConversion\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
                                                                     {"Date1", type date}, 
                                                                     {"Date2", type date}, 
                                                                     {"Date3", type date}, 
                                                                     {"Date4", type text}
                                                                     }
                                                 ),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date4", type date}}, "en-NZ")
in
    #"Changed Type with Locale"

In the script you can see the different of data type change using Locale which uses locale as the last parameter, instead of without locale. This brings a very important topic in mind; correct date type conversion is locale dependent, and you can get it always working if you mention Locale in date type conversion.

Summary

Date Type conversion can be tricky depends on the locale of system you are working on. To get the correct Date Type conversion recommendation is to use Locale for type conversion. some of formats might work even without using Locale. For example I have seen YYYYMMDD is working fine in all locales I worked with so far, but DDMMYYYY or MMDDYYYY might work differently.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.