Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Nozama
Helper I
Helper I

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. Untitled.png

 

 

1 ACCEPTED SOLUTION
SundarRaj
Super User
Super User

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.

SundarRaj_0-1739854798050.pngSundarRaj_1-1739854816502.pngSundarRaj_2-1739854855954.png

 

Sundar Rajagopalan

View solution in original post

6 REPLIES 6
SundarRaj
Super User
Super User

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.

SundarRaj_0-1739854798050.pngSundarRaj_1-1739854816502.pngSundarRaj_2-1739854855954.png

 

Sundar Rajagopalan
AlienSx
Super User
Super User

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

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.

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
uzuntasgokberk
Super User
Super User

Hello @Nozama ,

 

You can do it on the power query screen. Here are the examples:

The data:

uzuntasgokberk_0-1739809671152.png

Unpivot other (Cat)

uzuntasgokberk_1-1739809704791.png

Add a year. İf you don't, power query understands it is current year.

uzuntasgokberk_2-1739809739327.png

Change the type:

uzuntasgokberk_3-1739809755009.png

 

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"

 

uzuntasgokberk_0-1739813108776.png

 

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 |

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.