Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Power Query: Sort a Column by Another Column

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,021
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query: Sort a Column by Another Column


    Power Query: Sort a Column by Another Column
    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 by legalhustler; Jul 3rd, 2018 at 01:31 AM.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,109
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Sort a Column by Another Column

    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.
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,021
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Sort a Column by Another Column

    Quote Originally Posted by Matt Allington View Post
    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 by legalhustler; Jul 3rd, 2018 at 11:44 AM.

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,838
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Power Query: Sort a Column by Another Column

    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?

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,021
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query: Sort a Column by Another Column

    Quote Originally Posted by VBA Geek View Post
    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 by legalhustler; Jul 3rd, 2018 at 02:24 PM.

  6. #6
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,838
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Power Query: Sort a Column by Another Column

    Quote Originally Posted by legalhustler View Post
    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 by VBA Geek; Jul 3rd, 2018 at 02:54 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •