Locked lesson.
About this lesson
You've got a bare bones functional model, but what is with the order of the months? Who sorts their dates in the following order: Apr, Aug, Dec, Feb? You do if you haven't told Power Pivot how to sort things properly!
Exercise files
Download this lesson’s related exercise files.
Sorting Data Model Fields.xlsx829.7 KB Sorting Data Model Fields - Completed.xlsx
874 KB
Quick reference
Sorting Data Model Fields
An overview of methods for sorting columns in Power Pivot.
When to use
When you want to sort a column based on another column instead of by the default method.
Instructions
By default, Power Pivot sorts in ascending alphabetical or numeric order
Groundwork
- You must have another column by which to sort your column of values
- Your “sort by” column must have values that match the “values to sort” column in every instance (a one to one relationship)
Configure a Custom Sort order
- Open Power Pivot and go to the table containing the data to sort
- Select the column you wish to sort differently
- Go to Home --> Sort by Column
- Select the column on which you are basing the sort
- Close Power Pivot and the Pivot Table should display the new sort order
Hints & tips
- You must have a One to One relationship between the two columns you are sorting
- 00:04 At the end of last video I said everything is working perfectly, and
- 00:09 it is sort of, if that makes sense.
- 00:11 Let's go take a quick look over here and see what I'm talking about.
- 00:15 I wanna add a couple of slicers to this particular set up here.
- 00:19 So I'm gonna go to pivot table tools, analyze and I'm gonna insert two slicers,
- 00:25 one for my year, and the other one for my month name.
- 00:29 I'm not gonna use timelines just yet.
- 00:30 I wanna use slicers for
- 00:32 a specific reason that will become clear a couple of modules from now.
- 00:36 In this case here, I've got these two slicers.
- 00:38 So I'm gonna grab them, I'm gonna pull them up to the top here, and I'll just
- 00:42 align them to the top so that they're the same height or level of the same place.
- 00:47 And I'll just shorten this guy up here so you've got these guys looking like they're
- 00:52 about the same height, that looks pretty good.
- 00:54 And of course, I'll make the changes that I usually do with my size and
- 00:58 properties to make sure that they don't automatically end up getting sliced
- 01:02 around and sized differently.
- 01:04 And finally, I'll grab Month Name and just give him a few extra columns.
- 01:09 Cuz I think this will start to show some of the challenges that I'm actually
- 01:13 looking at here.
- 01:14 Everything looks pretty cool.
- 01:17 I can drill in to 2009 and if I were to go and
- 01:20 add the Month Name to the bottom of my pivot table here.
- 01:25 You can see that I can drill into April or August or July and
- 01:29 everything is slicing nicely.
- 01:31 I can even grab contiguous months, like August and December,
- 01:36 which plainly exist together on On the calendar, right?
- 01:40 Or not.
- 01:41 This is the challenge, now,
- 01:43 suddenly we start seeing that not only is my slicer out of order, it's actually
- 01:48 in perfect order if you like alphabetical order but so is my pivot table.
- 01:53 And if I were to grab multiple months here,
- 01:55 it would be doing the exact same thing.
- 01:57 It's not picking them up because of how it's showing in the slicer,
- 02:01 it's because this is a text field and
- 02:03 its sorting the way that Power Pivot knows how to sort text which is alphabetical.
- 02:08 So what do we do if we wanna see things look different?
- 02:13 Well, the answer to that is we actually go into Power Pivot and we go into Manage.
- 02:21 Within Power Pivot you need to find the fields that you're actually using.
- 02:26 So in this particular case these fields are on my calendar table.
- 02:31 And inside here you'll notice that if I select the Month Name column,
- 02:36 there's this button on the Home tab that lights up called, Sort by Column.
- 02:41 This is how we actually control how the sorting is done in Power Pivot.
- 02:46 We're gonna grab a sort by column, and say,
- 02:49 I would like to sort the Month Name column that I've selected by a different column.
- 02:53 This is says, all right, so that's Month Name,
- 02:55 which column would you like to sort it by?
- 02:57 Now, the important thing around this is,
- 02:59 there has to be a one-to-one relationship between these two values.
- 03:03 So in this case, January is always 1, February is always 2, March is always 3.
- 03:08 So that's no problem.
- 03:10 Let's go in and let's grab this and sort by month number.
- 03:13 And we'll say OK.
- 03:17 Unfortunately, nothing really changes in here, so
- 03:19 it's hard to see that you've actually done this.
- 03:22 And yet if we close Power Pivot, and return to Excel,
- 03:25 what you'll see is that this has changed the sort order for both my slicer and
- 03:30 for my pivot table, so everything looks much better.
- 03:33 Now, I do wanna show you something really quickly here.
- 03:37 I'd like to go back and decide, hey you know what?
- 03:39 Maybe I wanted to add something else to my calendar.
- 03:41 So I'm gonna go back to data.
- 03:43 Show my queries.
- 03:45 I'm gonna edit my calendar for a second.
- 03:48 And I'm gonna add two new fields to it.
- 03:51 So I'm gonna grab the date, go to Add, choose the Date.
- 03:56 We're gonna look at Day and we'll grab the Day.
- 04:00 And we're going to go to Day, we're gonna grab another one called Day of Week.
- 04:05 And we're also gonna grab the state, day, and I'm gonna grab name of day.
- 04:12 Again, I'd like to see this become a little shorter, so
- 04:17 we'll go back to Transform and extract the first three characters.
- 04:24 And now we'll say Home > Close Mode.
- 04:26 So again this just speaks to the fact that you can go
- 04:29 back if you decide you need more fields at any time,
- 04:32 you can always go back to Power Query and make a quick modification.
- 04:35 And now, when you jump into Power Pivot, what you're gonna see is that we've got
- 04:40 all of these new fields and this is really cool.
- 04:43 But I wanna show you what happens when you grab something like the day name column
- 04:47 and you say, I need to sort this.
- 04:49 And I need to sort this, obviously,
- 04:51 the correct key to sort this by is gonna be Day.
- 04:53 And we say OK.
- 04:55 And it comes back and it says I can't do that because at least one value in day
- 04:59 name has multiple distinct values in day, whatever the heck that means.
- 05:02 Well, what it means is this,
- 05:06 if I filter day name down to just Friday.
- 05:10 Notice in the day column that we've got values and if you look through this table
- 05:14 long enough, you'll find that there's values from 1 to 31.
- 05:16 So Power Query says, or Power Pivot rather,
- 05:19 says I don't know which one of these things you want me to sort by.
- 05:23 But notice beside it, the day of week column.
- 05:25 Every time it's Friday, it's always 5.
- 05:28 So in this case, it makes sense to say day name,
- 05:32 we're gonna sort by day of week and that will work.
- 05:37 And this is one of those keys.
- 05:38 This one is a really easy one to actually relate to.
- 05:41 But if you're trying to sort city names or you're trying to sort provinces or
- 05:45 shift names or something like that,
- 05:46 it's really important to remember you have to have that one-to-one relationship
- 05:51 between those two individual columns in order to sort hem.
- 05:53 Once you do, you'll land out sort layer in there.
- 05:57 And at that point it is applicable to every slicer, every pivot table field,
- 06:00 everything that you ever put in here.
- 06:02 It will roll through your entire model perfectly.
- 06:05 Everything will be just exactly fine.
- 06:06 So you only have to set it up once, and
- 06:08 after that, say if we went in to take a look at these guys, they're all gonna sort
- 06:12 out quite nicely in the individual setup that we actually want.
Lesson notes are only available for subscribers.