Forum Discussion
mespiritu
2 years agoHelper I
Help sort 12 rolling months in order
Can someone help assist with how to sort the following by earliest month year Jul23 to latest June24 - I'm trying to sort by Attritubue using Jul23 as the first month to June24 as the last...
collinsg
2 years agoSolution Sage
Good day mespiritu ,
As it stands "Rolling months" does not give a unique sort order for "Attribute" as it provides no distinction between the same month name in different years. To provide such a distinction you could
- Add a custom column (which I call "Sort Date") which converts "Attribute" to the first of the month.
- In Table View sort "Attribute" by "Sort Date".
Here is sample code...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8irNMTJW0lEyUYrViVZyLE0H88zAPJCcCZBnDJcD8YyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}}),
#"Added Sort Date" = Table.AddColumn(#"Changed Type", "Sort Date", each Date.FromText([Attribute],[Format="MMMyy"]), type date)
in
#"Added Sort Date"
This yields,
Hope this helps
mespiritu
2 years agoHelper I
Hi there, I actually used the sample code above in advanced editor, but only receiving Jul23-Aug24 attributes. What is the correct code to get dates from Jul23 to Aug24?