Forum Discussion
Sorting column display order
Is there any way I can get PowerQuery to recognise that these column headers are MMM-YY and sort them in a timeline fashion? I have this kind of data for 10+ years and it changes dynamically, so looking for a dynamic automatic solution. Thanks.
Hi, here's the M code solution for the question. I am attaching three images. One for the source data, next for the output and last one for M code.
6 Replies
Hello Nozama ,
You can do it on the power query screen. Here are the examples:
The data:
Unpivot other (Cat)
Add a year. İf you don't, power query understands it is current year.
Change the type:
Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |Great, thanks! My apologies, I should have been more clear. The headers are supposed to be MMM-YY. So Feb-27 is February 2027, Mar-27 is March 2027, Jun-27 is June 2027, Oct-25 is October 2025, Mar-26 is March 2026 and Mar-28 is March 2028. So I want them to be sorted as Oct-25, Mar-26, Feb-27, Mar-27, Jun-27 and Mar-28, in chronological order. These will change from time to time so hoping for an automatic solution. Hope that makese sense!
Hello Nozama ,
Allright. I'm sharing M code because there are some additional steps. I hope it helps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIFYnOlWJ1opSQgyxiIDZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat = _t, #"Feb-27" = _t, #"Mar-15" = _t]), #"Unpivoted other columns" = Table.UnpivotOtherColumns(Source, {"Cat"}, "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted other columns", "Custom", each if [Attribute] = "Feb-27" then "Feb-27-2024" else if [Attribute] = "Mar-15" then "Mar-15-2025" else null, type text), #"Changed column type 1" = Table.TransformColumnTypes(#"Added Conditional Column", {{"Custom", type date}}), #"Sorted rows" = Table.Sort(#"Changed column type 1", {{"Custom", Order.Ascending}}), #"Inserted Month Name" = Table.AddColumn(#"Sorted rows", "Month Name", each Date.MonthName([Custom]), type text), #"Inserted Day" = Table.AddColumn(#"Inserted Month Name", "Day", each Date.Day([Custom]), Int64.Type), #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Inserted Day", "MonthNameDay", each [Month Name] & " - " & Text.From([Day])), {{"MonthNameDay", type text}}), #"Choose columns" = Table.SelectColumns(#"Added custom", {"Cat", "Value", "MonthNameDay"}), #"Changed column type" = Table.TransformColumnTypes(#"Choose columns", {{"Value", type number}}), #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type", {{"MonthNameDay", type text}}), List.Distinct(Table.TransformColumnTypes(#"Changed column type", {{"MonthNameDay", type text}})[MonthNameDay]), "MonthNameDay", "Value", List.Sum) in #"Pivoted column"Kind Regards,
Gökberk Uzuntaş
📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!
🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |
Hi Nozama
You have to create a sort column using this formula:
MMM_YY_Sort = YEAR([Date]) * 100 + MONTH([Date])
Then, go to your MMM-YY column, open the Column Tools in the ribbon, and under Sort By, select this newly created sort column (MMM_YY_Sort).
This ensures the MMM-YY text column displays correctly while sorting in chronological order.
Table.ReorderColumns( Source, List.Sort(Table.ColumnNames(Source), (x) => Date.FromText(x, [Format = "MMM-yy"])) )Hi, here's the M code solution for the question. I am attaching three images. One for the source data, next for the output and last one for M code.
