- Have you heard of the Microsoft family of Power BI products like Power View, Power Query, Power Pivot but don’t understand how they work together?
- Are you wondering how Power BI Desktop is different from “Power” in Excel?
Today’s post is all about the Microsoft Power BI family of products. Why post about this? Well, they are good products (and they are free with Office), but there have been so many terminology changes, updates, version changes, etc that it’s hard to know what’s what. Therefore, I will explain the “Power” products – Power Query, Power Pivot, Power View, Power BI Desktop — within the context of Excel and make sense of all the other “power” monikers that you have might have heard of. This post will not compare the Power tools to other BI tools like Spotfire, Tableau or Qlik, but I will post those comparisons in the future.
Power View is a data visualization technology that lets you create interactive charts, graphs, maps, and other visuals that bring your data to life. Power View is available in Excel, in SharePoint, SQL Server, and Power BI. The following links and information can get you going with Power View. I created a short table in Excel 2013, positioned the cursor in the table and clicked Inset/Power View Report. I get a status window saying Excel is opening a Power. Aug 19, 2013 - The Excel 2013 box (aka Excel 2013 standalone SKU) will now. Contain Power Pivot and Power View see this blog post on the Excel support team blog. I was able to download the Power Query add-in from the Microsoft. The PowerPivot and Power View Add-Ins for Excel 2013. Choose File→Options→Add-Ins or press Alt+FTAA. Click the Manage drop-down list button and then select COM Add-Ins from the drop-down list before you select the Go button. Select the check box in front Microsoft Office PowerPivot for Excel 2013 and then click OK.
How to enable the Excel 2013 Power View add-in and create a few basic visualizations. Getting Started with Power View in Excel 2013 MicrosoftBI. Unsubscribe from MicrosoftBI?
I’ll lead off by saying, I thought this was going to be a quick and easy write-up, as I’ve gotten a lot of good information out of this book. It is Microsoft, however, and I should have known better. I will warn you this post is long, but it will provide everything needed to get a handle on the Microsoft Power BI family of products.
Let’s start out with some high level context.
How To Add Power Pivot In Excel
- The suite of Microsoft Power BI tools includes – Power Query, Power View, Power BI Desktop, Power Map, Power Pivot, and Power BI Dashboards. They may not necessarily go by those names currently, but this is the context, so bear with me.
- Power BI began with Excel 2010 via the Power Query and Power Pivot add ins.
- Power Maps and Power Viewer were added in Excel 2013
- Power Query became “native” in Excel 2016.
- Power Pivot is still an add in in Excel 2016. Power Map and Power View are native in Excel 2016.
- Until July 2015, Excel was the only environment/software where Power products were deployed, but them came Power BI Desktop.
- Power BI Desktop is a separate piece of Microsoft software from Excel. Users download, install on their machine, and then click on an icon to open it just like Excel.
- Power BI Desktop is a different environment with which to access the Power Query and Power Pivot. Power Query and Power Pivot have the same functionality in both tools.
- Power BI Desktop also includes Power BI Dashboards, which is a lot like Power View in Excel but has a lot of additional functionality.
- I find this change to be the most confusing because Microsoft uses the term Dashboards to refer to pages where many different visualization types and text areas can be deployed.
- Power BI Dashboards include many visualizations not available in Power View in Excel, and it is much more highly configurable and customizable.
- Power BI Dashboards can also be deployed to the web or mobile.
- Power BI Desktop integrates with R.
- Power BI Desktop is a very different environment from Excel that will look at feel similar to other BI/Analytics applications like Tableau and Spotfire.
Some folks like to describe Power BI Desktop as Excel on steroids, but I like to think that Excel slowly evolved and changed for business needs until those need just became so different from the original product that something new needed to be created.
With that said, let’s answer the most basic of all questions. Why would I (or my company) use Power BI Desktop? Why would I stop using Excel? The answer is that you probably won’t stop using Excel. You will probably use both. Power BI Desktop is free after all. Really?!?!??! Yes, really. You can download the Power BI Desktop from this website.
That covers the high level bits. Now, let’s dive into the details – Power Pivot, Power Query, Power Map, Power View, Power BI Dashboards. Most of this information can be found in the book I linked to above, which is written by the folks at Power PivotPro. I have provided links to other blogs and websites where necessary.
Power Pivot
What does it do: Power Pivot allows users to import data from multiple data sources into a single Excel workbook, create relationships between data sources, create calculated columns and measures using formulas, build PivotTables and PivotCharts so data can be analyzed. The power here is being able to connect directly to data sources without IT intervention.
In Excel: Power Pivot is an add-in for Excel 2010-2016. If you don’t see it in your Ribbon, follow the steps below (note, they work for Power Pivot or Power Query — depending on your version of Excel).
Excel 2016
If you don’t see Power Pivot them in your own Excel, follow these steps to make them visible.
- Make sure to download the Add-in
- Go to the File menu
- Click Options and then Add-ins, and it looks like this…
- In the manage drop down select COM add-ins and click Go, and you should see this menu
- Click the checkboxes for the Power add ins. This is what my ribbon looks like…
In Power BI Desktop: Users can import Excel workbooks that contain Power Query queries, Power Pivot models and Power View worksheets. All Power Pivot external data connections will be converted to queries in Power BI Desktop, and users will have the same functionality to create relationships, etc as in Excel Power Pivot.
You may also hear Power Pivot referred to as the DAX Engine. Microsoft has somewhat dropped the use of the name Power Pivot, as Power Pivot is being included in more and more products, such as Power BI Desktop. You won’t hear Microsoft refer to Power Pivot within the context of Power BI Desktop, but it’s in there.
Download Power View For Excel 2013 64 Bit
Now for the fine print…Power Pivot is not available in all Excel versions. This MS Office blog will tell you: “No matter what Office plan you decide to purchase, you can leverage Excel’s powerful data analysis tools to help you discover, organize and analyze the data around you and find the business insights that matter. “ This is not actually true. If you purchase a personal version of Office 365, Power Pivot is not included. Here is a good blog post from those PowerPivotPro guys I mentioned earlier that specifies which version of Office do and don’t have Power Pivot.
Power Query
What does it do: By itself, Power Pivot can connect to data sources and pull in data, but data isn’t always ready to be pulled straight into an application and used. Sometimes it requires massaging, transforming, aggregating, and clean up. This is where Power Query steps in and assists users with transforming raw data for analysis. Power Query is optional. If all of your data is served up neat and tiny, perhaps in a data warehouse or cubes, Power Query might not be necessary. It could also be a huge help.
In Excel: In Excel 2013, Power Query was an add-in with it’s own menu in the ribbon. It went native in Excel 2016, and its functionality is now part of the Get & Transform section on the Data tab. It disappeared because Microsoft retired the Power Query name. It still exists under the hood but is not called out distinctly. In Excel 2016, Power Query doesn’t have its own ribbon. Its functionality exists in the Get and Transform functions on the data ribbon.
Excel 2013
Excel 2016
In Power BI Desktop: Power Query tasks can be performed in the Query Editor window of Power BI Desktop, specifically in the “Get Data” and “Queries” buttons.
In researching this post, I also found a product created by the PowerPivotPro guys called Power Update attempts to fill the gaps where Power Query falls short. Power Query may not be the best at automating the refresh of Excel workbooks. I’m not diving into Power Update or endorsing a product, but if you find it in a Google search, now you know what it is.
Visualizations in Excel
Power Query is all about getting the data. Power Pivot doesn’t offer any visualization options, only tabular views and calculations, and is best thought of as a calculation layer. However, Power Map and Power View are visualization layers available in Excel.
Power Map
What it does: It’s a map chart, plain and simple.
In Excel: Power Map was added in Excel 2013, but it not available in all versions of Excel. This blog post explains which versions of Office 2013 contain Power Map). Power Map was renamed 3D maps in Excel 2016, and it now looks like screen shot in the ribbon.
In Power BI Desktop: The map is one of the many visualization types available in Power BI Dashboards (the globe button).
Power View
What it does: Power View renders Power Pivot data into interactive dashboards.
In Excel: Power View was added to the ribbon with Excel 2013 (Pro Plus only). It was located in the Ribbon, under Insert, in the Reports section. It was removed from the ribbon in Excel 2016. It now looks like this…
Note, it is not part of the ribbon as a default, you need to make sure you have the add-in enabled and the add Power View to the ribbon in a custom group. Steps are located here.
The same site also notes that all of the functionality of Power View is now available as part of Power BI Desktop. It will continue to be supported in Excel 2016, but it doesn’t specify whether it will be continued in the future. If you find yourself using Power View, consider taking a stronger look at Power BI Dashboards in Power BI Desktop.
Power BI Dashboards
What it does: The Power BI Dashboard encompasses all that you can do in Power BI Desktop. It is the concept of having multiple visualizations on one page and having all of that content be highly customizable. The visualizations are more interactive the Excel visualizations, which are entirely static. Users can integrate data from multiple sources, add text comments and integrate R scripts.
In Excel: Power BI Dashboards are not available in Excel.
In Power BI Desktop: Power BI Dashboards is simply an improved version of Power View available only in Power BI Desktop. The Dashboard is where everything comes together. It is the finished product … the result of Power Query, Power Pivot with a dash of Power Map, visuals you would have put together with Power Viewer, and you own custom touches.
Closing Bullet Points
- All of the Power Pivot versions are available in 32-bit and 64-bit. When downloaded, you will see two version – 32-bit labeled as “x86” and 64-bit labeled as “AMD64”.
- 64-bit is more stable and support larger volumes of data.
- You CANNOT run 64-bit Power Pivot with 32-bit Excel and vice versa, so make sure before diving in, you are aware of the following:
- Your version of Office (Pro, Pro Plus, Home, etc)
- Your version of Excel (which year and which “bit”)
- Your version of Power Pivot (which “bit)
- Power BI Desktop updates very, very frequently. Check for updates.
- I have seen the Power suite of products spelled with and without a space between “Power” and the second word. I hemmed and hawed over which spelling to use and eventually just dropped it because spell check was easier.
- I didn’t discuss the Power BI Service specifically. This is simply a cloud based version of all other products that have been discussed.
And finally, I’ll just say that I fully expect folks to continue using Excel. Even though I have years of experience using other tools, sometimes Excel is just my tool of choice because I know how to do things quickly in it, but Power BI Desktop significantly improves upon the functionality of Excel for analytics and visualizations. I hope you found this post useful. Please use comments for any corrections or additional info.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!
As a follow-up to question 28375061 I cannot see the PowerView button on the Insert tab. I don't even see a Reports button group.I've verified the following:
- Office subscription = Office 365 Pro Plus
- Excel version 15.0.4841.1002
- Microsoft Office PowerPivot for Excel 2013 add-in is active
- the Power View add-in is active
- the registry entries referenced in MSKB article 2805999 exist
- the two registry entries for PowerView are set to LoadBehavior 3 (not 2)
- I have full permission to the two registry entries for PowerView (EE question 3837506)
(The link for MSKB article 2805999 couldn't be used in the bulleted list above because of nested tags.)
Is there anything else I can check? PowerPivot works just fine.
Thanks!