Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date formatting

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

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

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

      i still nead YYYY-MM-DD

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        4 years ago

        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