Convert Date to specific text format without creating new column

[PhilC]

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

[TommiBerlin]

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"))

[Anonymous]

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

[Nathaniel_C]

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]

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 · Accepted solution]

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"

[Digger]

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

Details:
23/09/2022

[Anonymous]

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

[PhilC]

Hi Mike,  @Anonymous 

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

Cheers

[Nathaniel_C]

@PhilC ,

So you need to get the date format changed first?

Nathaniel

[Nathaniel_C]

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.