Date formatting

[Anonymous]

Hi,

I have a table (data) like this:

I want to use first row as headers. But before that I want to change the date formats in that row as text as it looks (yyyy-mm-dd).

It there an easy way to do that?

Thanks in advance.

BR,

anbananand

[Anonymous · Accepted solution]

HI @Anonymous 

In the Power Query Editor, convert this as data column and split the columns to month, date and Year and rearrange as Year,month and date.

Then select these 3 columns and right click -> select merge option. 

Promote the first row to header, the format will be retained.

Thanks

Raj

[mahoneypat]

Just convert those columns to text before you promote headers (remove any existing Changed Type steps that convert them to Date type).  This assumes your original data is in the desired format.

Pat

[Anonymous]

@mahoneypat  thanks for your reply, the the format is automatically changed as below (MM/DD/YYYY):

i still nead YYYY-MM-DD

[mahoneypat]

Here is one way to do it after it is already promoted to headers in MM/DD/YYYY format.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.  You will want to use the Renamed Columns step in your query.

Note - you probably want to unpivot these columns anyway (which would simplify things).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMzk5MT1XSUTLUN9I3MjAyBDLN9A3NIOxYnWglR5AkEBspxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",List.Zip({Table.ColumnNames(#"Promoted Headers"), List.Transform(Table.ColumnNames(#"Promoted Headers"), each if Text.Contains(_, "/") then Date.ToText(Date.FromText(_), "yyyy-MM-dd") else _)}))
in
    #"Renamed Columns"

Pat