Power Query: Sort a Column by Another Column

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,074
In Power Query, how can I sort one column by another column? My end goal is to display my Month Name column (i.e. Sept, Oct, Dec etc) by the Fiscal Month Number (i.e. 1, 2, 3 etc ) column as a slicer so that the month name in the slicer shows Sept first, then Oct, then Dec, etc.
 
Last edited:

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,216
Are you using power pivot as the final output? If so, load the name column and the numeric column, then sort the name column by the numeric column from the power pivot window.
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,074
Are you using power pivot as the final output? If so, load the name column and the numeric column, then sort the name column by the numeric column from the power pivot window.
I suppose I could add my power query table to the data model then use Power Pivot to sort the column by another column (I know that exists) and create a slicer from there but I wanted to see if it's possible in PQ? Thanks.
 
Last edited:

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
You have a pivot table linked to a Power Query and then you are adding slicer on the MonthName which is sorting alphabetically but instead you want it from Sep to August?
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,074
You have a pivot table linked to a Power Query and then you are adding slicer on the MonthName which is sorting alphabetically but instead you want it from Sep to August?
Correct. I have pivot table from a Power Query connection only load and I want the slicer to show months in the order of the fiscal year months like Sept, Oct, Nov, Dec, Jan, Feb etc instead of showing Jan, Feb, Mar etc. first.
 
Last edited:

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Correct. I have pivot table from a Power Query connection only load and I want the slicer to show months in the order of the fiscal year months like Sept, Oct, Nov, Dec, Jan, Feb etc instead of showing Jan, Feb, Mar etc. first.

If you begin with a table like the below (named after you):



The below M will apply a transformation on the Month column such as that when you add a slicer on the pivot table the months will be sorted from September to August:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="legalHustler"]}[Content],
    
   SortedMonths = List.Reverse({"Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"}),


    HashFn = (T as text) as text => Text.Repeat(
                                                Character.FromNumber(129),
                                                List.PositionOf(SortedMonths,T,Occurrence.First)+1
                                                ) & T,


    Transfm = Table.TransformColumns(Source,
                                    {"Month",HashFn}
                                    )    


in
    Transfm
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,535
Messages
5,445,060
Members
405,309
Latest member
chandhana20

This Week's Hot Topics

Top