Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need the sort to start from Aug-July and i have this already and it wont start from Aug:
Data tab:
Really appreciate youre help
Michelle.
Solved! Go to Solution.
Hi @michellerob2000 ,
Have you solved your problem?
If not, you can try to achieve this by using this M function in Power Query:
Table.Sort(#"Changed Type", {each List.PositionOf({"August", "September", "October", "November", "December", "January", "February", "March", "April", "May", "June", "July"}, [Month])})
And the final output is as below:
Here is the whole M function in the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZCxDsIgEIbfhblLacE6NjEOJurg2HSghKhJbRssJn17BQ45GG758vHf/XQdOYnJCL2RgpS/qRnpCwypHe7gUQ0aq01OrVt79yy0fIBYsgRZy0vtop9jmhaQlRin8HDLk8I2DueaSYFSUUyss2uAjCFmzzChsXZr7ua9ptafuX0+/aaWVb0GpdO7MLZ2VTp8lescXSgaoa/q4GX+4FiaQxp3HZTEKqQiCt/cfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, ID = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
#"Sort Table" = Table.Sort(#"Changed Type", {each List.PositionOf({"August", "September", "October", "November", "December", "January", "February", "March", "April", "May", "June", "July"}, [Month])})
in
#"Sort Table"
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @michellerob2000 ,
Have you solved your problem?
If not, you can try to achieve this by using this M function in Power Query:
Table.Sort(#"Changed Type", {each List.PositionOf({"August", "September", "October", "November", "December", "January", "February", "March", "April", "May", "June", "July"}, [Month])})
And the final output is as below:
Here is the whole M function in the Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZCxDsIgEIbfhblLacE6NjEOJurg2HSghKhJbRssJn17BQ45GG758vHf/XQdOYnJCL2RgpS/qRnpCwypHe7gUQ0aq01OrVt79yy0fIBYsgRZy0vtop9jmhaQlRin8HDLk8I2DueaSYFSUUyss2uAjCFmzzChsXZr7ua9ptafuX0+/aaWVb0GpdO7MLZ2VTp8lescXSgaoa/q4GX+4FiaQxp3HZTEKqQiCt/cfwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, ID = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
#"Sort Table" = Table.Sort(#"Changed Type", {each List.PositionOf({"August", "September", "October", "November", "December", "January", "February", "March", "April", "May", "June", "July"}, [Month])})
in
#"Sort Table"
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Create a "Fiscal Year Month" column and sort by that. So "20/21-01" could be August 2020 and "20/21-12" could be July 2021.