Forum Discussion

jrduboue's avatar
jrduboue
New Member
9 years ago
Solved

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 int...
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

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

  • edhans's avatar
    edhans
    Community Champion
    7 years ago
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.