In the previous article, we saw how to create custom sorting order using a separate month table.Now we will see how a new type of table called Time Table can be an even better solution. Estimated reading time 7 min
Contents
Recap
The objective is to sort months in the right order and NOT in alphabetical order. We saw two methods of doing this: Custom List based sort order and Related Months table with an ordering column.
In this article , another related concept is explained. The concept is simple. We need to perform different type of analysis based upon dates and time.
The Need
The transactional files will usually have a specific date time as a timestamp to indicate the time at which something happened.
While analyzing data, however, we need to look at the time and date portion at a higher level. Higher level could be days, months, weeks, quarters etc.
In Pivot table, this level of grouping was done by Pivot Table itself. However in Power Pivot, we have to manage this grouping ourselves The Group… option is missing in Power Pivot.
Usually, any grouping requirement could be satisfied by adding a calculated column in the raw data which contains the requisite function like Month() or Year(). However that will make the data size explode and reduce performance. Too many calculations will need to be done for each row.
Therefore, a new approach is needed. The idea is to use a table containing ALL the dates in the analysis period. The first column is these dates and additional columns contain all kinds of useful grouping information. Here is how a Time Table looks. You can create your own or use it from various samples provided by Microsoft. Time tables are also available for download on Azure Marketplace.
Download files and follow along
This contains no data in the worksheet. Go to Power Pivot – Data Model to view the data.
Transactions.xlsx contains data with a date column and amount column. For simplifying the example, all other transaction details are omitted.
The time table covers every day from 1st January 2007 to 31st January 2015. First column contains date and rest of the columns are either grouping information or sorting information.
In this article, we just need to get the Month column and sort it on MonthNumber column for each row in the transaction table.
The Quarter column is interesting. It is based upon financial year starting in April instead of January. We could also have had a simple column containing zero or one called “working day”. But we will discuss Time Table usage in another article.
Let us focus on getting the Month for each transaction and sorting it in the correct order.
The process
It is easier to show the process as a video. Open the sample file – go to the data model in Power Pivot and follow the video.
View the video in full screen mode at HD resolution (720 p) if possible.
[youtube https://www.youtu.be/8uf3dSlJnRo&]
What Next
In future articles, I will cover the concept of Power Pivot in greater detail.
***
3 Responses
Reblogged this on SutoCom Solutions.
Thank you for addressing my question! I have one problem: my version of PowerPivot does not have the Sort by Columns option in my PowerPivot window! My company is still on Excel 2010 and I believe I am on V1. I’m checking online and it sounds like there is a v2 of the PP add-on. I will have to work with my company to get this installed on my workstation (or wait until I get Office 2013). I agree it is such an elegant solution to create a relationship between the date on the transactional table and the standardized date table. It makes it so much easier to change the view from day of the week, month, quarter…the date table I use also incorporates “week of” and “week number.”
Dear Dr. Nitin
I really still confuse about Power Pivot and Power Query. Which is better than the other? Because I recognize that both of them can connect many table together.
So, can you advice me about features of each one?
Thank you so much.
P/s: I am sorry for my stupid question 🙁