Converting specific text to date in PowerQuery

[jrduboue]

Hello all,

I have a text column with dates in the current format: "mddyyyy" or "mmddyyyy" or "0" (to indicate no date in the cell). Can I get assistance with how to convert these text strings into date values in the format "mm/dd/yyyy" or "null".

Example: 2012016 to 02/01/2016 and 0 to null or blank

Thank you in advance.

[v-caliao-msft · Accepted solution]

Hi @jrduboue,

You can use Date.FromText to achieve this requirement. It returns a Date value from a set of date formats and culture value, following ISO 8601 format standard.
Date.FromText(date as nullable text, optional culture as nullable text) as nullable date 

Supported formats

• yyyy-MM-dd

• YYYYMMDD

• M/d/yyyy

Reference
https://msdn.microsoft.com/en-us/library/mt260703.aspx

Regards,

Charlie Liao

[Anonymous]

not very useful. Our date format may vary such as: yymmdd, yyyymmmdd, ddmmmyy, ddmmyyyy,.... And we would like to get dates from our origin column in a specified format. Is there kind of function like: DateFromString(DateColumn, "yymmdd"). We can easily do this in other languages.

[edhans]

---

Supported formats

• yyyy-MM-dd

• YYYYMMDD

• M/d/yyyy

---

FWIW, it also supports "MMM DD YYYY" and appears to correctly convert the DD to a valid day whether it is two or one digits, i.e., "02" or " 2"

I don't know the full support for Date.FromText() conversion, but add this one to the list.