Chapter 3 of the book is full of rants telling you why you wouldn't run every future data set through PowerPivot. PowerPivot's inability to automatically sort by custom lists is my number one complaint.
PowerPivot will not automatically sort your months into custom list sequence, which means that you will get month names arranged in alphabetical sequence of April, August, and so on. This video shows the eight-click workaround.
PowerPivot will not automatically sort your months into custom list sequence, which means that you will get month names arranged in alphabetical sequence of April, August, and so on. This video shows the eight-click workaround.
Transcript of the video:
PowerPivot for the Data Analyst 3: Custom Month Sorting.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Okay, well, Chapter 3 in the book is called, "Why Wouldn't I Just Do Every Pivot Table through PowerPivot"?
And, it really is where I, kind of, gripe about a lot of the things where PowerPivot falls short.
And the one thing that PowerPivot really, really falls short on, is the inability to sort automatically by the custom list field.
Now, let me show you what I mean.
We have a date field here, and I need to convert that date field to month names.
Now, in Excel, of course, this would be the Text function.
In PowerPivot they have many, many functions.
All of the functions are named exactly as they're named in Excel, except for this one-- they misspelled this one.
Instead of calling it Text, they called it Format; and that kind of shows they are, you know, Visual Basic people instead of excel people.
They're using the Visual Basic Format function.
So, I create a little function here that says, FORMAT, and then give it the in-quotes, "MMM" format, and that will convert our data to a month name.
Alright, so, we'll let that calculate-- it's calculating 1.8 million rows, now.
One frustrating thing is, once we get that answer, the name of the column is going to be called Calculated Column 1.
So, you always want to right-click there, and we will rename that column-- I'll just call it "Month".
Okay, so, now we're going to create a pivot table from this data-- click OK, we'll choose that we want to see revenue, and that by Month.
And to our horror they don't put the months in the right sequence.
It goes: April, August, December, its alphabetical.
Alright, and we've just taken it for granted that pivot tables always put this data in the right sequence.
And, what's happening is, pivot tables, by default, will use the custom lists to sort their items.
For some reason, the PowerPivot people didn't seem to know about custom lists.
And, so, they're not sorting by custom lists.
Now, there is a way to do it, but it takes 8 clicks-- I have to tell you, these are the 8 clicks that I hate more than anything because, I've had to do them so many different times inside of PowerPivot.
So, we come here to Row Labels, and we go to More Sort Options.
We want to sort descending by month, but then, click on More Options.
You have to un-check Sort Automatically every time the report is updated, and then we have the ability to say that this is going to be in order of Jan, Feb, Mar, April, May, June.
Click OK; click OK; click OK; and now everything is sorted correctly.
Although, it looks like I chose descending, so, into more sort options, and say Descending by Month; again, more options; make sure that's still January, February, March.
Click OK; Click OK.
Alright, so, that's-- what-- even more than eight clicks because I screwed it up the first time.
This is so frustrating to me that they cannot manage to sort that into the custom list sequence.
And, hey, If you're reading one of the books by one of the SharePoint authors, I know-- I know-- that they have a suggestion here: They say that their Month Calculation should be Equal, Month of that field over there, and that will give you months like 1, 2, 3, 4, 5.
To me, that's proof that they've never actually worked in an accounting department.
Clearly, your manager is not going to accept a report where you have months like 1, 2, 3, 4, 5, even if you will combine 1-January, or 2-February, that's not going to fly-- they need months spelled out, that's what they expect, that's what they want.
Very disappointing, that PowerPivot doesn't automatically sort that data.
Well, hey, I wanna thank you for stopping by.
See you next time, for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Okay, well, Chapter 3 in the book is called, "Why Wouldn't I Just Do Every Pivot Table through PowerPivot"?
And, it really is where I, kind of, gripe about a lot of the things where PowerPivot falls short.
And the one thing that PowerPivot really, really falls short on, is the inability to sort automatically by the custom list field.
Now, let me show you what I mean.
We have a date field here, and I need to convert that date field to month names.
Now, in Excel, of course, this would be the Text function.
In PowerPivot they have many, many functions.
All of the functions are named exactly as they're named in Excel, except for this one-- they misspelled this one.
Instead of calling it Text, they called it Format; and that kind of shows they are, you know, Visual Basic people instead of excel people.
They're using the Visual Basic Format function.
So, I create a little function here that says, FORMAT, and then give it the in-quotes, "MMM" format, and that will convert our data to a month name.
Alright, so, we'll let that calculate-- it's calculating 1.8 million rows, now.
One frustrating thing is, once we get that answer, the name of the column is going to be called Calculated Column 1.
So, you always want to right-click there, and we will rename that column-- I'll just call it "Month".
Okay, so, now we're going to create a pivot table from this data-- click OK, we'll choose that we want to see revenue, and that by Month.
And to our horror they don't put the months in the right sequence.
It goes: April, August, December, its alphabetical.
Alright, and we've just taken it for granted that pivot tables always put this data in the right sequence.
And, what's happening is, pivot tables, by default, will use the custom lists to sort their items.
For some reason, the PowerPivot people didn't seem to know about custom lists.
And, so, they're not sorting by custom lists.
Now, there is a way to do it, but it takes 8 clicks-- I have to tell you, these are the 8 clicks that I hate more than anything because, I've had to do them so many different times inside of PowerPivot.
So, we come here to Row Labels, and we go to More Sort Options.
We want to sort descending by month, but then, click on More Options.
You have to un-check Sort Automatically every time the report is updated, and then we have the ability to say that this is going to be in order of Jan, Feb, Mar, April, May, June.
Click OK; click OK; click OK; and now everything is sorted correctly.
Although, it looks like I chose descending, so, into more sort options, and say Descending by Month; again, more options; make sure that's still January, February, March.
Click OK; Click OK.
Alright, so, that's-- what-- even more than eight clicks because I screwed it up the first time.
This is so frustrating to me that they cannot manage to sort that into the custom list sequence.
And, hey, If you're reading one of the books by one of the SharePoint authors, I know-- I know-- that they have a suggestion here: They say that their Month Calculation should be Equal, Month of that field over there, and that will give you months like 1, 2, 3, 4, 5.
To me, that's proof that they've never actually worked in an accounting department.
Clearly, your manager is not going to accept a report where you have months like 1, 2, 3, 4, 5, even if you will combine 1-January, or 2-February, that's not going to fly-- they need months spelled out, that's what they expect, that's what they want.
Very disappointing, that PowerPivot doesn't automatically sort that data.
Well, hey, I wanna thank you for stopping by.
See you next time, for another netcast from MrExcel.
