Excel: Power BI And How Can I Get Them?

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Power BI is the collective name for a series of impressive add-ins for Excel 2010/2013. Unfortunately, Microsoft Marketing continues to fumble any attempts to communicate what the add-ins are and how you can get them.

Let's start with what the add-ins do:

  • Power Query is a data-cleansing tool that helps you load imperfect data from many sources into Excel. As you go through the steps of cleaning the data on the initial import, those steps are recorded in a new programming language called "œM". The next time you need to load and clean the data, you simply have to Refresh and all the steps are carried out. Available as a free download for anyone with Excel 2010/2013/2015.
  • Power Pivot allows you to create pivot tables from very large data sets. You can join two data sets without using VLOOKUP. You can write amazing new calculated fields in a new DAX formula language. Power Pivot was a free download for Excel 2010. It is impossible to get in the "œowned" version of Office and tricky to find in the "œrented" versions of Office 365.
  • Data Model is a subset of Power Pivot that lets you create pivot tables from two worksheets. It comes built-in to all versions of Office 2013. You can not build DAX in this version.
  • Power View is an interactive dashboard product designed to compete with Tableau. It debuted in some SKUs of Office 2013. A typical Version 1 product from Microsoft, it has some cool features but needs improvements, hopefully in Office 2015. It is not available for Excel 2010 and follows the same tricky distribution model as Power Pivot for 2013.
  • Power Map is a way to plot a pivot chart on a map. You can build tours of the data and save to a video. It is a free download for anyone with any version of Excel 2013 and is built-in to Excel 2015.

All of these run in the desktop version of Excel. If you want to share your analyses with people who don't have Excel, you can publish them to a Power BI site via hosted SharePoint. This $40 a month option is still in beta as this book goes to press in 2014.

Power Pivot had been a free download for anyone with Office 2010. In Office 2013, they cut Power Pivot out of many consumer versions of Excel. If you bought Office 2013 Standard, Office 2013 Professional, Office 365 Home Premium, Office 365 Small Business, Office 365 Student you will never have access to Power Pivot or Power View. Here are ways to get Power Pivot in Office 2013:

  • Buy a stand-alone boxed version of Excel 2013 from Amazon. I personally lead the campaign to remind Microsoft Marketing that the long-standing rule is that the boxed version of Excel stand-alone is always supposed to have everything. Even the student version of Excel 2013 has Power Pivot. Priced anywhere from $68 to $105, this is a one-time payment and will insure you have Power Pivot forever.
  • If your company has a volume licensing agreement with Microsoft, you can purchase the E3 or E4 level of Office 2013 and you will get Power Pivot and Power View. You need to buy five licenses of something to qualify for volume licensing. One strategy is to buy one copy of Office 2013 E3 and four copies of Windows Vista DVD Player (the cheapest item in the catalog).
  • Rent your copy of Office. Subscribe to the Office 365 Pro Plus plan at $12 a month. Do not get fooled into the $12.50 a month Office 365 Small Business. Even though it is more expensive, it does not include Power Pivot or Power View.
  • Subscribe to Office 365 Mid-Size Business, Office 365 Enterprise E3 or Office 365 Enterprise E4.

For more resources for Microsoft Excel