BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 16 January 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Create a calendar table for PowerPivot in Excel / Access
I've just blogged about creating a table for a calendar in SQL Server - here's how to do the same thing in Excel or Access. Whether you're creating a table in Excel or Access, the easiest place to start is Excel.
Creating an Excel Calendar
To do this, start by creating a dates column:
Click and drag on the autofill handle shown to create a sequence of dates. It's often a good idea to extend this into the future, so your calendar is futureproof.
Now create any additional columns you will frequently want to aggregate by. Here's how to do this:
Type a formula referring to the top cell, and copy it down.
When copying formulae down, don't click and drag on the autofill handle; instead, double-click on it, and Excel will automatically copy your formulae down to the last date in column A.
Some Useful Excel Functions
Here are some functions you may find useful:
| To obtain | Use and copy down | Example |
|---|---|---|
| Year | =YEAR(A2) | 2013 |
| Month number | =MONTH(A2) | 1 |
| Month name | =TEXT(A2,"mmmm") | January |
| Month name for sorting | =TEXT(A2,"mm - mmmm") | 01 - January |
| Day number | =DAY(A2) | 16 |
| Day name | =TEXT(A2,"dddd") | Wednesday |
| Quarter | ="Q" & INT(MONTH(A2)+2)/3) | Q1 |
When you've finished creating your calendar, save it (eg as calendar.xlsx):
Double-click with the cross shown to copy the various formulae down.
Importing the Calendar from Excel
To link to the calendar in PowerPivot, choose to connect From Other Sources:
In PowerPivot, choose this button on the Home tab of the ribbon.
Scroll all the way down to the bottom of the list, and choose Excel:
Choose to connect to Excel.
Browse to the calendar file you've just saved:
Make sure you tick the box saying that your first row has column headers!
You can now give your imported sheet a friendly table name, and proceed as for all other PowerPivot tables:
Here we've chosen to call the imported data Calendar.
The disadvantage of using Excel is that you can't connect to the same table twice in a PowerPivot workbook (so if you have two date fields in the same workbook, you'd have to copy the Excel workbook and link to that instead for the second date).
Creating a Calendar Table for Access
One way to do this would be to write lots of VBA to generate the table, fields and data, but the easier way is to follow the steps above to create an Excel calendar workbook. You can then import this as a table in Access:
First choose to import Excel external data.
Now choose which Excel workbook you're importing:
Browse to find the Excel calendar workbook, and choose to import it into a new table.
The rest of the wizard is pretty self-explanatory. It's worth tidying up your data types:
Access assumes the year, for example, is of data type Double, but it's actually an Integer. However, you could leave all the types as their defaults and set sensible defaults after importing the table into PowerPivot.
It's probably a good idea to set the date column as your primary key, as this will check that you haven't inadvertently included the same date twice.
Eventually, you'll have a shiny new table in Access:
The calendar table in design view.
Connecting to the Access Calendar Table
Once you've created your calendar in Access, you can link to it in PowerPivot as for any other table:
Choose this option to import tables from an Access database.