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

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.