The terms “Power Query”, “Power Pivot”, “Power BI” and other “powers” are increasingly popping up in articles and materials about Microsoft Excel. In my experience, not everyone clearly understands what lies behind these concepts, how they are interconnected and how they can help a simple Excel user.
Let’s clarify the situation.
Power Query
Back in 2013, a specially created group of developers within Microsoft released a free add-in for Excel. Power Query (other names are Data Explorer, Get & Transform), which can do a lot of things useful for everyday work:
- Upload data in Excel from almost 40 different sources, including databases (SQL, Oracle, Access, Teradata…), corporate ERP systems (SAP, Microsoft Dynamics, 1C…), Internet services (Facebook, Google Analytics, almost any websites ).
- Collect data from Files all major data types (XLSX, TXT, CSV, JSON, HTML, XML…), both singly and in bulk – from all files in the specified folder. From Excel workbooks, you can automatically download data from all sheets at once.
- Clean up received data from “garbage”: extra columns or rows, repetitions, service information in the “header”, extra spaces or non-printable characters, etc.
- Bring data into order: correct case, numbers-as-text, fill in gaps, add the correct “cap” of the table, parse “sticky” text into columns and glue it back, divide the date into components, etc.
- in every possible way transform tables, bringing them into the desired form (filter, sort, change the order of columns, transpose, add totals, expand cross tables to flat and collapse back).
- Substitute data from one table to another by matching one or more parameters, i.e. nice replacement function VPR (VLOOKUP) and its analogues.
Power Query is found in two versions: as a separate add-in for Excel 2010-2013, which can be downloaded from the official Microsoft website, and as part of Excel 2016. In the first case, after installation, a separate tab appears in Excel:
In Excel 2016, all the functionality of Power Query is already built in by default and is on the tab Data (Date) as a group Get and convert (Get & Transform):
The possibilities of these options are completely identical.
The fundamental feature of Power Query is that all actions for importing and transforming data are stored in the form of a query – a sequence of steps in the internal Power Query programming language, which is succinctly called “M”. Steps can always be edited and replayed any number of times (refresh query).
The main Power Query window usually looks something like this:
In my opinion, this is the most useful add-on listed in this article for a wide range of users. A lot of tasks for which you had to either terribly distort with formulas or write macros are now easily and beautifully done in Power Query. Yes, and with subsequent automatic updating of the results. And considering it’s free, in terms of price-quality ratio, Power Query is simply out of competition and an absolute must-have for any intermediate-advanced Excel user these days.
PowerPivot
Power Pivot is also an add-in for Microsoft Excel, but designed for slightly different tasks. If Power Query is focused on importing and processing, then Power Pivot is needed mainly for complex analysis of large amounts of data. As a first approximation, you can think of Power Pivot as a fancy pivot table.
The general principles of working in Power Pivot are as follows:
- We are the first loading data in Power Pivot – 15 different sources are supported: common databases (SQL, Oracle, Access …), Excel files, text files, data feeds. In addition, you can use Power Query as a data source, which makes the analysis almost omnivorous.
- Then between loaded tables connections are configured or, as they say, is created Data Model. This will allow in the future to build reports on any fields from existing tables as if it were one table. And no VPR again.
- If necessary, additional calculations are added to the Data Model using calculated columns (similar to a column with formulas in a “smart table”) and measures (an analogue of the calculated field in the summary). All this is written in a special Power Pivot internal language called DAX (Data Analysis eXpressions).
- On the Excel sheet, according to the Data Model, the reports of interest to us are built in the form pivot tables and diagrams.
The main Power Pivot window looks something like this:
And this is how the Data Model looks like, i.e. all loaded tables with created relationships:
Power Pivot has a number of features that make it a unique tool for some tasks:
- In Power Pivot no line limit (as in Excel). You can load tables of any size and easily work with them.
- Power Pivot is very good at compress data when loading them into the Model. A 50MB original text file can easily turn into 3-5MB after downloading.
- Since “under the hood” Power Pivot, in fact, has a full-fledged database engine, it copes with large amounts of information very fast. Need to analyze 10-15 million records and build a summary? And all this on an old laptop? No problem!
Unfortunately, Power Pivot is not yet included in all versions of Excel. If you have Excel 2010, you can download it for free from the Microsoft website. But if you have Excel 2013-2016, then it all depends on your license, because. in some versions it is included (Office Pro Plus, for example), and in some it is not (Office 365 Home, Office 365 Personal, etc.) You can read more about this here.
Power Maps
This add-on first appeared in 2013 and was originally called GeoFlow. It is intended for visualization of geo-data, i.e. numerical information on geographical maps. The initial data for display is taken from the same Power Pivot Data Model (see the previous paragraph).
The demo version of Power Map (almost no different from the full one, by the way) can be downloaded completely free of charge again from the Microsoft website. The full version is included in some Microsoft Office 2013-2016 packages along with Power Pivot – in the form of a button 3D map tab Insert (Insert — 3D-map):
Key features of Power Map:
- Maps can be both flat and voluminous (globe).
- You can use several different visualization types (histograms, bubble charts, heat maps, area fills).
- You can add time measurement, i.e. animate the process and watch it develop.
- Maps are loaded from the service Bing Maps, i.e. You need a very fast internet connection to view. Sometimes there are difficulties with the correct recognition of addresses, because. the names in the data do not always match those of Bing Maps.
- In the full (non-demo) version of Power Map, you can use your own downloadable maps, for example, to visualize visitors to a shopping center or prices for apartments in a residential building right on the building plan.
- Based on the created geo-visualizations, you can create videos directly in Power Map (example) to share them later with those who do not have the add-in installed or include them in a Power Point presentation.
power view
First introduced in Excel 2013, this add-in is designed to bring your data to life with interactive graphs, charts, maps, and tables. Sometimes the terms are used for this. dashboard (dashboard) or dashboard (scorecard). The bottom line is that you can insert a special sheet without cells into your Excel file – a Power View slide, where you can add text, pictures and a lot of different types of visualizations based on your data from the Power Pivot Data Model.
It will look something like this:
The nuances here are:
- The initial data is taken from the same place – from the Power Pivot Data Model.
- To work with Power View, you need to install Silverlight on your computer – Microsoft’s analogue of Flash (free).
On the Microsoft website, by the way, there is a very decent training course on Power View in .
Power BI
Unlike the previous ones, Power BI is not an add-in for Excel, but a separate product, which is a whole set of tools for business analysis and visualization. It consists of three key elements:
1. Power BI Desktop – a program for analyzing and visualizing data, which includes, among other things, all the functionality of the Power Query and Power Pivot add-ons + improved visualization mechanisms from Power View and Power Map. You can download and install it for free from the Microsoft website.
In Power BI Desktop you can:
- Load data from over 70 different sources (as in Power Query + additional connectors).
- bind tables to model (like in Power Pivot)
- Add additional calculations to data with measures и calculated columns on DAX (as in Power Pivot)
- Create beautiful data based interactive reports with different types of visualizations (very similar to Power View, but even better and more powerful).
- Publish created reports on the Power BI Service site (see next point) and share them with colleagues. Moreover, it is possible to give different rights (reading, editing) to different people.
2. Power BI online service – to put it simply, this is a site where you and each user in your company will have their own “sandbox” (workspace) where you can upload reports created in Power BI Desktop. In addition to viewing, it even allows you to edit them, reproducing almost all the functionality of Power BI Desktop online. You can also borrow individual visualizations from other people’s reports here, collecting your own author’s dashboards from them.
It looks something like this:
3. Power BI Mobile is an application for iOS / Android / Windows for connecting to the Power BI Service and conveniently viewing (not editing) the created reports and dashboards right on the screen of your phone or tablet. You can download it (completely free) here.
On an iPhone, for example, the report generated above looks like this:
And all this while maintaining interactivity and animation + imprisoned for touch and drawing on the screen with a pen. Very comfortably. Thus, business intelligence becomes available to all key persons of the company at any time and in any place – only Internet access is needed.
Power BI pricing plans. Power BI Desktop and Mobile are free out of the box, and most Power BI Service features are free too. So for personal use or use within a small company, you don’t need to pay a penny for all of the above and you can safely stay on the plan Free. If you want to share reports with colleagues and administer their access rights, you will have to go to BESS ($10 per month per user). Is there some more Premium – for large companies (> 500 users) that require separate storage and server capacities for data.
- Project Gantt Chart in Excel with Power Query
- How to create a database in Excel using Power Pivot
- Visualization of movement along the route on the map in Power Map