At a glance
Question
Is it worth learning about PowerPivot?
Answer
If you deal with data, yes it is. PivotTables are a powerful data analysis tool. PowerPivot takes PivotTables to another level. I usually describe PowerPivot as a PivotTable on steroids (in a good way). PivotTables are usually limited to reporting on a single table, but PowerPivot can report on multiple tables and has a new formula language that is designed to work with relational databases.
Excel 2013 introduced a Relationships feature in the Data ribbon tab that enables PivotTables to report on multiple tables. I described this feature in the October 2015 Excel Yourself column. Excel 2013 and Excel 2016 have built-in data models. The data model seamlessly handles relational databases and related tables. When you install PowerPivot in Excel 2010 – it’s a free Microsoft add-in – you also have the data model.
The Power series
PowerPivot is part of a series of Power add-ins made available with Excel in the past six years. The Power Excel series includes:
- Power Query: Allows you to automatically perform data cleansing operations on internal and external data sources.
- PowerPivot: Allows you to report on multiple data sources in a single report. You can interact directly with relational databases via Excel.
- Power View: Allows you to create interactive charts and reports based on the data model.
- Power Map: Allows you to visualise geographic data using maps.
The term “Modern Excel” is used to describe these features as they have extended Excel’s reporting capabilities far beyond those of the old-fashioned spreadsheet.
Part of the Power series that isn’t an Excel add-in is Power BI (Business Intelligence). This is a separate application outside of Excel that allows you to create interactive dashboards and reports based on your Excel files, plus PowerPivot reports and Power Query data.
Power BI incorporates all the other Power products and allows you to bring them together in a single reporting system. It has been described as self-service Business Intelligence. The reports can also be published online. Currently, Power BI is free via the web. There is also a free desktop version – see www.powerbi.microsoft.com.
Microsoft’s attitude to updates is changing. Features are being added at a faster pace than previously. Microsoft’s Office subscription service, Office 365, now receives constant upgrades.
To have access to all the Power features, you need the Office 365 ProPlus subscription. Not all versions of Excel can run the full range of Power features. Microsoft has made accessing these features more difficult and complex than it needs to be.
PowerPivot
The beauty of PowerPivot is that you still use the same PivotTable interface you are used to. You have access to the data model, which can have multiple related tables.
PowerPivot can report on multiple tables from within a single database or tables from separate databases, provided there’s a common field between the tables. For example, you can bring in tables from an SQL database and then bring in a CSV file that has been downloaded from another system.
You can then create a single report combining the SQL tables and the CSV file. Even if there is no common field, you can create a mapping table in Excel that maps a code from one table to the code of another. This mapping table can then be used to create a relationship between the two tables and enable a combined report.
"There are very few software packages available that can provide this type of flexibility."
There are very few software packages available that can provide this type of flexibility – certainly none that are as cheap, popular and easy to use as Excel. PowerPivot enables you to treat Excel as your data hub and bring together all your data sources into a single reporting package. Excel and PowerPivot work seamlessly with most databases.
When you start working with PowerPivot, you may need to involve your IT department. It can supply permissions, passwords and folder paths to ensure you can access your data. Establishing the connection to a data source is usually only done once.
You also need a good understanding of the relational database structure. This is an ongoing learning experience. The more you use PowerPivot, the more tables you will want to access. Large, modern databases can have hundreds of related tables. Your database administrator can help you understand the structure of the database.
PowerPivot doesn’t affect your data in any way. It is read-only – you extract the data to create your reports. Using data direct from the source ensures your reports are accurate and up to date.
You should, of course, still build in validations to make sure your reports are correct. If you are using the wrong table or the wrong field (column), you may be extracting and reporting the wrong data.
To use PowerPivot effectively, you need to have a good working knowledge of relational databases in general. This can be a steep learning curve. In my training, I have found some people understand relational databases fairly quickly, while others can take some time to reach their “light bulb” moment.
Combining data from multiple systems – even publicly available data – can help you identify relationships and trends that are difficult to spot when the data is viewed in isolation. For example, you could combine weather information for temperatures and rainfall with your daily sales data to identify relationships or trends. These combined reports could help you develop predictive algorithms for your budgeting and forecasting.
Even though Excel sheets can only handle just over one million rows, PowerPivot can handle tens of millions. Additionally, PowerPivot can handle hundreds of millions of rows when you install the 64-bit version of Excel.
32-bit vs 64-bit
The 32-bit version of Excel is the program’s most common version and is compatible with more systems. Some IT departments will only roll out the 32-bit version because of compatibility issues. The 64-bit version can handle huge data sets running into hundreds of millions of rows of data.
This requires large amounts of RAM and a fast CPU. If you are unable to install the 64-bit version of Excel on your company’s system, then, with your IT department’s permission, it may be worth investing in a standalone computer or laptop with high specifications that you can install 64-bit Excel on and use for your large reporting requirements.
DAX formulas
Another significant feature of PowerPivot is the Data Analysis Expressions (DAX) formula language. You can create DAX formulas that work across tables. DAX functions are similar to Excel’s native functions, but they are built to work with relational database table structures, not spreadsheet structures. DAX formulas can easily handle filtering and conditional calculations.
Much of what you learn in PowerPivot can be directly applied to Power BI if you decide to use that for your dashboards and reporting. Power BI uses the same DAX formulas. Note that Power BI is currently being upgraded more frequently than PowerPivot.
Books
I recommend books as the best way to learn PowerPivot and DAX. I own and recommend the books listed below:
- Microsoft PowerPivot for Excel 2010: Give Your Data Meaning by Marco Russo and Alberto Ferrari (Microsoft Press, A$38)
- PowerPivot Alchemy: Patterns and Techniques for Excel by Rob Collie and Bill Jelen (Holy Macro! Books, A$29)
- Microsoft Business Intelligence Tools for Excel Analysts by Michael Alexander, Jared Decker and Bernard Wehbe (Wiley, A$47)
- The Definitive Guide to DAX: Business Intelligence with Microsoft Excel, SQL Server Analysis Services and Power BI by Marco Russo and Alberto Ferrari (Microsoft Press, A$54)
- DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution by Rob Collie (Holy Macro! Books, A$19.50)
- DAX Patterns 2015 by Alberto Ferrari and Marco Russo (Loader, A$35)
Kindle books
There are free Kindle reading apps for most platforms. The PC and iPad apps make reading textbooks easier as they display the images better. When you use the Kindle app on your PC or laptop, you can copy formulas between the ebook and Excel.
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]
