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/yyyy") as the conversion code.

 

Tried using Transform and Change Type on the column and edit the resulting code, but keep getting an error, so must not have the syntax correct.

 

Cheers

 

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"

10 Replies

  • 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"
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    let
    Source = let StandardDate = (dateColumn as text,DateSchema as text) =>
    let

    MyDelimiter = Text.Range(DateSchema,Text.PositionOfAny(DateSchema,{"/","-",".","\","_"}),1),
    DateList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(dateColumn),
    SchemaList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(DateSchema),
    TableDate = Table.FromColumns({DateList,SchemaList}),
    myDay = "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "D" )[Column1]{0}),
    myDD = Text.Range(myDay,Text.Length(myDay)-2,2),
    MyMonth= "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "M" )[Column1]{0}),
    myMM = Text.Range(MyMonth,Text.Length(MyMonth)-2,2),
    myYear= "20" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "Y" )[Column1]{0}),
    myYYYY = Text.Range(myYear,Text.Length(myYear)-4,4),
    DateByParts = myYYYY & "-" & myMM & "-" & myDD,
    DateCleaned = Date.FromText(DateByParts)
    in DateCleaned
    in StandardDate
    in
    Source

  • TommiBerlin's avatar
    TommiBerlin
    New Member
    2 years ago

    My goal is to get the data for today (so whenever we update the datasource. As the combination of date & text in the web.Contens() is not possible, we had to convert to it to text in an "yyyy-MM-dd".
    Solved it like that.

    = Json.Document(Web.Contents("https://api.xxxx/client?json=1&from=" & Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd") & "&to=" & Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd") &"T23:59:59Z"))