Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Transform date into month and year format

Hi All,

 

i need to know, how Transform date into month and year format in excel query

 

 

  • negi007's avatar
    negi007
    5 years ago

    Anonymous Do you want seperate column for Month and Year in your data, you can do this using below lines in power query

     

    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type)

     

    Your output will be like below

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Hi All, i need to know, how Transform date into month and year format in excel query i try this 1. Extract the Year into 1 column & Month Number into a second column, then Merge the columns together with a “-” as a separator then its come as Apr-2019 but remain as text format, while transform into pivot table as report, then the report comes in April-2019, August -2019, but i need April, May, June
    • negi007's avatar
      negi007
      Icon for Community Champion rankCommunity Champion
      5 years ago

      Anonymous Do you want seperate column for Month and Year in your data, you can do this using below lines in power query

       

      #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
      #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type)

       

      Your output will be like below

       

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      Anonymous 

      Instead of creating those columns in Power Query, do it in the Data Model.

      Go to your table, add a new column for Year Month Sorting:

       

      YearMonthSort = YEAR([DATE]) * 100 + MONTH([DATE])

       


      Add the Year Month column to use in visuals

       

      Year Month = FORMAT([DATE],"MMM YYYY")

       

       

      Now select the Year Month Column and got the Column Tools tab and sort by YearMonthSort column, Done

      ________________________

      If my answer was helpful, please consider Accept it as the solution to help the other members find it

      Click on the Thumbs-Up icon if you like this reply 🙂

      YouTube  LinkedIn