Power Query: Sort a Column by Another Column

[legalhustler]

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.

 

[Matt Allington]

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]

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.

 

[VBA Geek]

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]

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.

 

[VBA Geek]

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:

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