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.

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:


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


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


More on Date Transformation using Power Query



@imVivRan


Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING