Forum Discussion
Help sort 12 rolling months in order
Good day mespiritu ,
The sample code gives this result in the query editor...
To reproduce this, create a blank query, open the Advanced Editor window and replace everything in it with the sample code.
The steps of the code work as follows.
- "Source" step: Creates a test table with a column "Attribute" with values Jul23, Aug23, Jul24, Aug24 and a column "Values" with values 4, 6, 3 and 2.
- "Change type" step: Sets the type of the "Values" column.
- "Added Sort Date" step: adds the "Sort Date" column.
If you wish to apply the 3rd step to your table, add the step to your query by pressing the fx button on the formula bar, paste in
= Table.AddColumn(#"Changed Type", "Sort Date", each Date.FromText([Attribute],[Format="MMMyy"]), type date)
and replace #"Changed Type" with the name of the preceding step in your query.
What was the error message?
Regards
Hi thank you for explaning above - what sample code can I use if I want my attributes from Jul23 to June24? and If I'd like to change up my attributes every month since I have to update dashboard monthly.
Good day mespiritu ,
One thing I hadn't noticed before was that your month names were a mix of 3 and 4 letter abbreviations. Your screenshot shows your data contains a column "Attribute" - so long as this column contains Jul23 to June24 then you need one line of code, which is modified from the previous code to cater for the 3/4 letter abbreviation mix.
= Table.AddColumn(#"Previous Step", "Sort Date", each Date.FromText(Text.Start([Attribute],3) & Text.End([Attribute],2),[Format="MMMyy"]), type date)
Select the last step in your query, press the fx on the formula bar, then paste in the code.
Hope this helps