Forum Discussion
Sorting months in Power BI using sort table
neeharikathota
Go to Power Query, Select the column that has dates like "DEC19", RIGHT-CLICK > Change Type > select Date. Now you can do the formatting in the data model as MMMYY.
Let me know if need further help on this.________________________
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 🙂
neeharikathota try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcklNNrRUitWJVvJKzDMyALPcUpNArFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthYear = _t]),
#"Convert To Date" = Table.TransformColumns(Source,{{"MonthYear", each Date.FromText(Text.Start(_,3) & "1, 20" & Text.End(_,2)), type date}})
in
#"Convert To Date"It takes the Dec19 and converts it to the string "Dec 1, 2019" then converts to a date. Jan20 becomes "Jan 1, 2020" and again, a date.
You can wrap a Date.EndOfMonth() around that if you want it to be Dec 31, 2019 after conversion, or replace the "1, 20" part of the formula with, say, "15, 20" to convert to Dec 15, 2019 before it converts.
becomes
Note the code also automatically converts it to a date column.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.