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.
Identifying Locale
The Following table highlights popular date locale formats:
Source: https://docs.oracle.com
You may refer to the following Wikipedia article for Locale information:
Transforming Column Using Locale
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:
Tip
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
