Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi, @bilalkhokar73 ,
You could add a index column sort by Year-Month.
1.add index in power query.group
2.sort
3.add index column and expand it.
Then sort by index column.
The final show:
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.
Hi,
Create a custom column using Date.ToText([Date], "yyyyMM") and use this column to sort ur Month Year Column
Pls refer:
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |