Sorting column display order

[Nozama]

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. 

[SundarRaj · Accepted solution]

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.

[AlienSx]

Table.ReorderColumns(
    Source, 
    List.Sort(Table.ColumnNames(Source), (x) => Date.FromText(x, [Format = "MMM-yy"]))
)

[techies]

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.

[uzuntasgokberk]

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 |

[Nozama]

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!

[uzuntasgokberk]

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 |