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"
Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

Hi PhilC ,

My standard date is mm/dd/yyyy. So: the first pic is date, second pic is text. Included is the code, but what I did was go to Date Type, and change it to text.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

 

  • 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

         

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      6 years ago

      PhilC ,

      So you need to get the date format changed first?

      Nathaniel

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago
        Table.AddColumn(#"Changed Type1", "Custom", each Date.ToText([Column1], "dd/MM/yyyy"))
        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN7LUNzIwtFSK1QFzDaG8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Column1], "dd/MM/yyyy")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}, {"Column1", type text}}),
    #"Custom1" = Table.TransformColumnTypes(#"Changed Type1",{{"Column1",each Date.FromText([Column1],"dd/MM/yyyy",}})
in
    Custom1

        PhilC , no success so far trying to emulate adding a custom column.