Sorting Data Model Fields

Quick reference

• 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.