Transform date into month and year format

[Anonymous]

Hi All,

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

[Anonymous]

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

@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]

@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