CODEWITHSUNDEEP
powerbidax
Neil P
Neil P

Reputation: 3210

DAX - how to convert a date string from another locale into a date type

I have a date string that fails to import because it is in a different format to that expected my the machines locale (i.e. US dates to a UK machine).

How do I tell DAX to convert this string into a date, but using a specified format or locale, different to the machines default.

For example, I would like to import

3/27/2008 11:07:31 AM

as

27/3/2008 11:07:31 AM

Upvotes: 1

Views: 3914

Answers (2)

Mike Honey
Mike Honey

Reputation: 15037

I think the most practical solution is in the Query Editor, but complex formula are not required.

I would Right-click the column and choose Change Type / Using Locale. Then I would specify Data Type = Date and Locale = English (United States).

Upvotes: 1

alejandro zuleta
alejandro zuleta

Reputation: 14108

You have two options.

First option, use the basic Formatting tab functionality in Power BI.

Select the column and use the below settings in the Formatting tab:

enter image description here

Second option (recommended), use PowerQuery to import the text column in datetime data type.

The following expression will split the text by "/" character, then will convert dd/mm/yyyy string to the datetime data type.

Table.AddColumn(#"Changed Type", "DateTime",
 each Text.Split([#"#(001A)Date Import"],"/"){1} & "/" 
  & Text.Split([#"#(001A)Date Import"],"/"){0} & "/" &
    Text.Split([#"#     (001A)Date Import"],"/"){2})

In this case I've added an additional column in order to import the column in the required datetime type, you can apply the changes to the same column though.

enter image description here

Date import column is the actual text column, DateTime is the column I've added to import Date Importas Datetime type.

If you get stuck check the official documentation about PowerQuery.

Let me know if this helps.

Upvotes: 2

Related Questions