Share via

Power Pivot Sort By Column Not Working in Excel 2016

Anonymous
2017-01-16T13:28:54+00:00

I'm trying to sort a Month value (e.g. Dec-16) in a date dimension using a Month Sort column. I can set the sort by column in the data model no problem but when I use the Month value in a pivot table it is sorting alphabetically

  • The month sort column is of data type & format a whole number
  • The date dimension is set as a date table
  • The cardinality between month value & month sort is 1:1
  • I have refreshed the model & pivot table
  • I have fiddled around with the sorting of Month Value (A>Z,Z>A etc.)
  • I'm using the latest build of Excel 2016 (7571.2072)

I've done this hundreds of times in SSAS Tabular & Power BI but not recently in PowerPivot. What could I be missing?

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2017-01-26T00:06:52+00:00

Hi folks,

I had one of our engineers take a look, and it turns out that my understanding as to how this works, and how it's supposed to work were incorrect.  The bad news is that Excel does not have precisely the feature we thought it had, but the good news is that there is a workaround that I think makes it possible to get the desired result with a little bit of work.  Let me explain:

  1. Sort one column by another is a feature that works within the Power Pivot window.  When you are browsing, sorting and filtering within Power Pivot, the sorting will obey any instructions you have provided to sort one column by another.
  2. Excel PivotTables do not have any knowledge of one column being sorted by another in the data model.  When you sort within an Excel PivotTable, the sorting is done entirely based on the data type, and also takes into account some very important lists that are maintained specifically to facilitate sorting of values that commonly need to be sorted in a special order.
  3. Whenever you are sorting in Excel, including within PivotTables, Excel does look at sorting lists, and if your data matches those lists, the lists are used instead of regular sorting.  There are built-in lists and also custom lists that you can create.  The reason that regular month names sort correctly in PivotTables is *not* because you've said to sort a month column by a month number column.  It happens because there is a built in list for sorting that contains the twelve names of the months.  The following lists are built in to the Excel product: Sun, Mon, Tue, Wed, Thu, Fri, Sat;  Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday;  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec;  January, February, March, April, May, June, July, August, September, October, November, December.  These built in lists is why
  4. In addition to the built-in lists, you can create your own custom lists to be used for sorting.  I have tried this, and if I create a list that includes Oct-15, Nov-15, Dec-15, Jan-16, Feb-16, etc. then sorting of month names in that format will work as you expect.
  5. Detailed information on how to create custom lists in Excel is available here: https://support.office.com/en-us/article/Create-or-delete-a-custom-list-for-sorting-and-filling-data-D1CF624F-2D2B-44FA-814B-BA213EC2FD61
  6. We are also considering improving sorting with some other options, but this sort of thing would be a feature in the future, and isn't something that we would be handling as an immediate bug fix.
  7. I recommend either configuring your data to use the built in lists, or adding a custom list with the month formats that you want.  Either approach will work.

Sorry that I didn't realize this when I first posted to this thread, but I'm happy to have figured this out.  I hope this helps you to understand what's going on under the cover in PivotTables based on data models in Excel.

Thanks,

-Howie

Was this answer helpful?

5 people found this answer helpful.
0 comments

24 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-01-20T15:32:11+00:00

    I had it working, but now it's not working again.  I'm going to have one of our engineers take look to figure out from the code (by running this in some debugging tools) exactly what's happening here.  I'll give you an update when I know more.

    Thanks again for sending me the file.  I needed that to be able to really see what's happening.

    Thanks,

    -Howie

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2017-01-20T15:18:25+00:00

    Thanks for sending this file.  I do indeed think that you have found a bug, but it's not where we were looking.  I needed the file to see this.

    I made a few changes, and the combination of my changes seems to have gotten things working correctly.

    I'll need one of our engineers to take a closer look but I think the bug is related to one of three things.  First, I think there could be a problem because you are using a "linked table" which is updated from the source every time that you switch between Excel and Power Pivot windows.  Second, I think there may be an issue with the apostrophe in the month name.  Third, you may have hit some other error along the way that caused some internal index to be corrupted.  This last option seems unlikely, but it's possible, especially with a linked table.

    What I tried:

    1. I deleted the linked tables from the model, and replaced them by copying/pasting the data tables from the worksheet into the model.
    2. I created a month name column that did not contain an apostrophe.  Instead of Feb'16, I use Feb16.  This is easily done in the date_dimension table in the model, by adding a calculated column whose formula is =FORMAT([Date],"MMM YY")
    3. I sorted the new month name column by month number.
    4. I created new PivotTables that use my new month name column.
    5. Sorting works as expected for me.

    Could you give this a try and let me know f this works for you as well?  I'll get the bug filed today, but you won't want to wait for the bug fix, you will just want to get this working in your workbook.

    Thanks,

    -Howie

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2017-01-18T16:23:48+00:00

    Hi Herbert

    Unfortunately I can't share my workbook with the community, however  I can replicate the issue in the workbook you shared. When you first add a sorted column to a pivot it does use the sort order column, but if you use subsequently use the A-Z / Z-A sorting button it sorts it alphabetically, when it should retain Sort by you specified in the model (see image below)

    If you can't replicate in your version then it might be an issue with my version / build of Excel.  If you can replicate then it would be good if MSFT can confirm this behaviour is intended (and different from the behaviour when the model is in Power BI or SSAS Tabular).

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2017-01-18T09:41:27+00:00

    Hi Yoga

    Thanks for looking into this but I think you have misunderstood the problem, I'm not looking for a workaround.  I should be able to specify the sort order of a column.  I have used this method many many times in Power BI and SSAS Tabular, but today I have to use Excel and the behaviour is unexpected.. possibly a bug?

    To replicate, in the sample I sent, open the Data Model and look for the Start Date dimension. I have a column for Start Month (a text value) and Start Months Ago (a whole number).  Using the Sort By Column function I have sorted Start Month by Start Months Ago, which should result in a chronological sort of the Start Month column however in the Pivot table it sorts alphabetically

    Thanks

    Edward Miles

    PS that link you previously posted just takes me to the Power Pivot support page, was there a specific article I should be looking at?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments