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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bilalkhokar73
Helper IV
Helper IV

Month-year Column Sorting from Desc to Asc

Hi,

 

I have Month Year Column in 2022-Jan, 2022-March this format

When I pull this column in Matrix then i want to sort this column from desc to asc

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @bilalkhokar73 ,

You could add a index column sort by Year-Month.

1.add index in power query.group

vyalanwumsft_6-1657094651035.png

 

2.sort 

vyalanwumsft_2-1657093955328.png

3.add index column and expand it.

vyalanwumsft_3-1657093973281.png

Then sort by index column.

vyalanwumsft_4-1657094279966.png

The final show:

vyalanwumsft_5-1657094296988.png

let
 Source = Excel.Workbook(File.Contents("C:\Program Files\Microsoft Power BI Desktop\bin\SampleData\Financial Sample.xlsx"), null, true),
    financials_Table = Source{[Item="financials",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(financials_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Discount Band", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", type number}, {" Sales", type number}, {"COGS", type number}, {"Profit", type number}, {"Date", type date}, {"Month Number", Int64.Type}, {"Month Name", type text}, {"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Y-month", each Date.ToText([Date],"yyyy-MMM")),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Year", "Month Number"}, {{"A", each _, type table [Segment=nullable text, Country=nullable text, Product=nullable text, Discount Band=nullable text, Units Sold=nullable number, Manufacturing Price=nullable number, Sale Price=nullable number, Gross Sales=nullable number, Discounts=nullable number, #" Sales"=nullable number, COGS=nullable number, Profit=nullable number, Date=nullable date, Month Number=nullable number, Month Name=nullable text, Year=nullable number, #"Y-month"=text]}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Year", Order.Descending}, {"Month Number", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Expanded A" = Table.ExpandTableColumn(#"Added Index", "A", {"Segment", "Country", "Product", "Discount Band", "Units Sold", "Manufacturing Price", "Sale Price", "Gross Sales", "Discounts", " Sales", "COGS", "Profit", "Date", "Y-month"}, {"Segment", "Country", "Product", "Discount Band", "Units Sold", "Manufacturing Price", "Sale Price", "Gross Sales", "Discounts", " Sales", "COGS", "Profit", "Date", "Y-month"})
in
    #"Expanded A"


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SelvakumaranM
Frequent Visitor

Hi,
Create a custom column using Date.ToText([Date], "yyyyMM") and use this column to sort ur Month Year Column

Pls refer:

https://radacad.com/generate-year-month-day-combinations-in-power-bi-report-using-power-query-date-t...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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