Power Query: Date Transformation using Locale(Text to Date)

Each country has a specific way of formatting the date. Depending on the default date setting of the laptop or desktop, Power Query may not recognize a date column as a date. There are cases when the data imported in Power Query some of the date fields are either formatted as text or shows error while transforming the column as a date:

When transforming it as a date column:

In this example, the date format for the column is dd/mm/yyyy, and the system’s default date format is mm/dd/yyyy.

Using Locale option handles transformations where the date format in the data table is either a text or in a different format from the default date format of the system.

Using Locale provides the option of selecting the appropriate date format and then transform it in date.

The Following table highlights popular date locale formats:

Source: https://docs.oracle.com

You may refer to the following Wikipedia article for Locale information:

https://en.wikipedia.org/wiki/Date_format_by_country

The example above has the date format with the locale setting of India.

Click on the Column Property Icon > Using Locale

In the Locale settings option > Select the Data Type as Date

Under Locale > Select English (India) > Click Ok

It transforms the column as Date with no errors:

Transform multiple columns at one go: Copy the highlighted section in the formula bar

Add the name of all the columns, separated by a comma:

In case the formula bar is not visible:

View > Check Formula Bar

More on Date Transformation using Power Query

@imVivRan