Forum Discussion

PhilC's avatar
PhilC
Resolver I
6 years ago
Solved

Convert Date to specific text format without creating new column

Hi,   I would find the syntax to convert an existing Date column into text, in the "dd/MM/yyyy" format, but do within the column, not by adding a new column.  Using Date.ToText([Start Date], "dd/MM...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    PQ is very sensitive to Date versus DateTime. Sometimes i have to convert to a DateTime. Below is some sample code.

    Regards,

    Mike

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Date" = Table.TransformColumns(Source,{{"myDate", each Date.ToText(Date.From(_),"dd/MM/yyyy") , type text}})
in
    #"Extracted Date"
PhilC's avatar
PhilC
Resolver I
6 years ago

Hi Nathaniel,

 

My dates are not currently formatted, so I have M and MM dates.  I need to have dd/MM/yyyy as I am going to compare to another dateset on a text basis, so am looking to have a consistent text format.

 

I have tried using Change Type on the column but it does not alter for format to expand M to MM with a preceeding 0, which is what I need.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

PQ is very sensitive to Date versus DateTime. Sometimes i have to convert to a DateTime. Below is some sample code.

Regards,

Mike

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Date" = Table.TransformColumns(Source,{{"myDate", each Date.ToText(Date.From(_),"dd/MM/yyyy") , type text}})
in
    #"Extracted Date"
  • PhilC's avatar
    PhilC
    Resolver I
    6 years ago

    Hi Mike,  Anonymous 

     

    That solved it for me.  The Date.From(_) bit was the key I think.  Can you explain that?

     

    Cheers

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Phil,

    I actually could not tell if your original data the any type, a text or a datetime. Date.From usually does not care, so I suggested using it.

    Hope this explanation helps,
    Mike

  • Digger's avatar
    Digger
    Post Patron
    3 years ago

    DataFormat.Error: We couldn't parse the input provided as a Date value.

    Details:
    23/09/2022