How to Resolve Locale Date Format Error in Power Query: A Step-by-Step Guide

Before diving into the steps for date formatting, we need to understand what is locale. Locale is used to display and identify the data type especially for data such as date, currency and numeric data, different locales have different ways of displaying data. For example, The United States is one of the countries that use MM/DD/YYYY, while in Malaysia, the date format is DD/MM/YYYY.

Microsoft Excel or Power Query by default is using the regional setting in your operating system. So if you have a dataset using United States format, while your operating system is operating using other regional format, you may get error when importing the dataset as Power Query is interpreting the data using the regional setting in your operating system. For example, if a dataset has a date "04/24/2024"(MM/DD/YYYY) while the regional format in your operating system is (DD/MM/YYYY), your Power Query would not be able to interpret the data and giving you error just like the image below.

There are 3 methods to change the date format:

• Change Type in Power Query Editor

• Redefine Locale Setting in Power Query Editor

• Changing Regional Setting in the Operating System (Windows or Mac).

Change Type

Import a dataset to Excel then select on Transform Data button.

At the Query Settings > Changed Type, make sure that the date type is in text. This step is important to avoid Power Query to interpret the data wrongly, if the data type for the date is in date format, then the Power Query would interpret the date wrongly and giving error which will impact further data cleaning steps.

Next, right click on the column, select Change Type > Using Locale

A dialog box "Change Type with Locale" will pop out.

At Data Type, select Date

At Locale, select the locale the original dataset is using.

• In my example below, the dataset was using United States format which is MM/DD/YYYY while the regional setting in my laptop is Malaysia format which is DD/MM/YYYY, so I changed the Locale to be English (United States) so that Power Query can interpret the data using United States date format (MM/DD/YYYY) then later on display them in my regional (Malaysia) date format (DD/MM/YYYY).

Then select OK after you have done the setting.

In the Query Settings, you shall see Changed Type with Locale.

Finally, the date column has been changed to date type without error!

However, note that, date type will be displayed in your operating system locale format.

Changed Type Operation is more suitable if you only have a few columns that are using other locale format. If you are dealing with a lot of columns and tables that are using other locale format, then you might need to consider the next method which is Redefine locale setting in Power Query Editor.

Redefine locale setting in Power Query Editor

First, import a dataset to Excel then select on Transform Data button.

In the Power Query Editor, go to File > Options and settings > Query Option

In the Query Options, go to Regional Settings, at the Locale select the locale that the imported data is using.

Then select OK.

After you have set the Regional Settings, all data that you're importing to this file will be interpreted based on the locale format you have set.

So you can straight away change the Date Type to Date, and Power Query will interpret the data based on the Locale setting you have set and display in your operating system Locale format. This applies to all the data you imported to the same file.

Redefine locale setting in Power Query Editor method is useful when you are dealing with a bunch of data using other locale format that will be imported in one particular file for further data analysis.

However, if moving forward all the data you will be dealing is in other locale format or maybe you're moving to some other region, you may want to change regional setting in your operating system, where all the format in your computer/laptop will be changed.

Changing Regional Setting in the Operating System

Changing regional setting in your operating system will impact on how your entire system display date/time, numeric, and currency data types.

This may also affect your previous files, if your previous files were formatted in your previous locale settings.

For Windows user, you may refer:

https://support.microsoft.com/en-us/office/change-the-windows-regional-settings-to-modify-the-appearance-of-some-data-types-in-access-databases-edf41006-f6e2-4360-bc1b-30e9e8a54989

For Mac user, you may refer:

https://support.apple.com/en-gb/guide/mac-help/intl163/mac

Conclusion, before cleaning data, you need to understand the data you are dealing with such as the format and unit used, so that you have clear idea of what's next when you are cleaning the data. Hope this post can help you with fixing data errors using different locale formats. And have fun analyzing data!