Format Date Column

[PPStar]

Hello. 

I have a dataflow in the power BI Service. There is a date column (Date) which needs to be as a text data type (dont ask why) and needs to be in a specific format. 

Currently the date column is in the dd/mm/yyyy format. 

I need to change this so its in the yyyy-MM-dd format , but as a string. 

I have added the following code

Text.From([#"Date"], "yyyy-MM-dd", "en-US") 

and i get the following error : 

Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.

Details

Reason = Expression.Error

Arguments = {#date(2015, 6, 21), "yyyy-MM-dd", "en-US"}

WHat am i doing wrong?

[_AAndrade]

Yes it's possible. Please try this:

Table.TransformColumns(Navigation, {[#"Date"], each Date.ToText(_, "yyyy-MM-dd")})

Instead of "Table.AddColumn(Navigation, "Custom", each Date.ToText([#"Date"], "yyyy-MM-dd"))"

[PPStar]

Whilst visually, this looks correct, i just tried it and it gives me this error : 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Details

Reason = Expression.Error

Any ideas

[_AAndrade]

I use tha name of my column Date where I want to transform.
Probably the error is on the [#"Date"]. Replace this column name for your column date name.

[PPStar]

I did that. My date column is called Y-M-D.

This is the code i am using 

 #"Custom Column" = Table.TransformColumns(Navigation, {[#"Y-M-D"], each Date.ToText(_, "yyyy-MM-dd")})

[_AAndrade · Accepted solution]

Try this: Table.TransformColumns(Navigation, {{"YYYY-MM-DD", each Date.ToText(_, "yyyy-MM-dd"), type text}}) and see if this solves your problem

[PPStar]

Thank you so much. That worked

[_AAndrade]

Hi,

Try this code:

Date.ToText([Date], "yyyy-MM-dd")

[PPStar]

Thanks. That worked. 

Do you know how i can alter the existing M code - so we dont create a new column

At the moment i have this M code

  #"Added custom" = Table.AddColumn(Navigation, "Custom", each Date.ToText([#"Date"], "yyyy-MM-dd")),

  #"Transform columns" = Table.TransformColumnTypes(#"Added custom", {{"Custom", type text}})

This create a new column called Custom. Whilst this works. I want to acheive the same without creating a new column

Can we just modify the existing column to ensure its in yyyy-MM-dd format and as text?