In PowerPivot, Dates will show up in Alphabetical order. However, in today's example, Bill shows us that this particular Data Set has Dates that are stored -not as Dates- but as Text. In Episode #1678, we learn how to work with these Text entries as Dates in PowerPivot.
...This episode is the video podcast companion to the book, "PowerPivot For The Data Analyst: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel. PowerPivot for the Data Analyst: Microsoft Excel 2010 by Bill Jelen
For all of your Microsoft Excel needs visit MrExcel.com
Your One Stop for Excel Tips and Solutions.
...This episode is the video podcast companion to the book, "PowerPivot For The Data Analyst: Microsoft Excel 2010", from Bill Jelen a.k.a. MrExcel. PowerPivot for the Data Analyst: Microsoft Excel 2010 by Bill Jelen
For all of your Microsoft Excel needs visit MrExcel.com
Your One Stop for Excel Tips and Solutions.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episodes 1678: Sort PowerPivot Text Dates.
All right, today's question was sent in, in response to an earlier video that we had about how to sort dates in PowerPivot. They always come up alphabetically but in this case the actual dates are stored and not as dates but really as text so literally in the cell, the original cell is Apr - 2012 and my old trick of more Sort options descending or ascending and then more sort options uncheck this and specify Jan, Feb, Mar won't work because that's not what's in the cell.
We have years in a cell.
So I want to offer an alternate solution.
I'm going to take these results, Ctrl+C go to New Worksheet and paste here Ctrl+V and we'll call this “OrigDate” and “Sort By.” Now here since everything is just in one year I could just put something like 4, 8, 12 - if I had a lot of these I could use =MONTH(DATEVALUE(A6)).
Either way we get some sort thing there.
If it was more than one year then you might want to sort by something like -- instead of the month we'll use the text of the date value and change the format to be yyyy - mm making sure to put two M's there so that way it stays sortable.
The formula will be =TEXT((DATEVALUE(A6)),”YYYY-MM” All right so just get some column there that will work.
Right now we're going to take this data and make it into a table, Ctrl+T and I'll call it “Calendar” On the PowerPivot tab we're going to add this to the data model.
Okay so now have two tables in here and we want to link text date to original date.
All right so we create a relationship and we'll go back to data view and say that this field, the original date wants to be sorted by the sort by column click OK.
All right, let's return back to Excel, take a look at our pivot table.
We are going to take the text date out and put the date from the calendar table in and you see that it's automatically sorted in the proper sequence.
You know I've always complained about this.
This was so easy with regular pivot tables ,pivot cache pivot tables.
Why does PowerPivot have such a problem with this?
I did originally just as a note try to add that calculated field here in the PowerPivot window using equal date value equal month but then when I try to sort by that calculated column I created a circular dependency and so no go there just creating a separate table seems to be the standard way to go.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episodes 1678: Sort PowerPivot Text Dates.
All right, today's question was sent in, in response to an earlier video that we had about how to sort dates in PowerPivot. They always come up alphabetically but in this case the actual dates are stored and not as dates but really as text so literally in the cell, the original cell is Apr - 2012 and my old trick of more Sort options descending or ascending and then more sort options uncheck this and specify Jan, Feb, Mar won't work because that's not what's in the cell.
We have years in a cell.
So I want to offer an alternate solution.
I'm going to take these results, Ctrl+C go to New Worksheet and paste here Ctrl+V and we'll call this “OrigDate” and “Sort By.” Now here since everything is just in one year I could just put something like 4, 8, 12 - if I had a lot of these I could use =MONTH(DATEVALUE(A6)).
Either way we get some sort thing there.
If it was more than one year then you might want to sort by something like -- instead of the month we'll use the text of the date value and change the format to be yyyy - mm making sure to put two M's there so that way it stays sortable.
The formula will be =TEXT((DATEVALUE(A6)),”YYYY-MM” All right so just get some column there that will work.
Right now we're going to take this data and make it into a table, Ctrl+T and I'll call it “Calendar” On the PowerPivot tab we're going to add this to the data model.
Okay so now have two tables in here and we want to link text date to original date.
All right so we create a relationship and we'll go back to data view and say that this field, the original date wants to be sorted by the sort by column click OK.
All right, let's return back to Excel, take a look at our pivot table.
We are going to take the text date out and put the date from the calendar table in and you see that it's automatically sorted in the proper sequence.
You know I've always complained about this.
This was so easy with regular pivot tables ,pivot cache pivot tables.
Why does PowerPivot have such a problem with this?
I did originally just as a note try to add that calculated field here in the PowerPivot window using equal date value equal month but then when I try to sort by that calculated column I created a circular dependency and so no go there just creating a separate table seems to be the standard way to go.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
