Forum Discussion
Converting specific text to date in PowerQuery
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.
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 dateSupported formats
-
yyyy-MM-dd
-
YYYYMMDD
-
M/d/yyyy
Reference
https://msdn.microsoft.com/en-us/library/mt260703.aspxRegards,
Charlie Liao
-
3 Replies
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 dateSupported formats
-
yyyy-MM-dd
-
YYYYMMDD
-
M/d/yyyy
Reference
https://msdn.microsoft.com/en-us/library/mt260703.aspxRegards,
Charlie Liao
Supported formatsyyyy-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.
-
