Power Query: Sort a Column by Another Column

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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):

ZhGW0YQ.png


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

Odsm6c4.png
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top