The terms “Power Query,” “Power Pivot,” “Power BI,” and other “Power” more often appear in articles and materials about Microsoft Excel. Not everyone clearly understands what is behind these concepts, how they are interrelated, and how they can help an ordinary Excel user. Let’s clarify the situation.
What is Power Query, Power Pivot, and Power BI, and Why do you Need Them?
Power Query is an ETL (Extract, Transform, Load) self-service tool that works like an Excel add-in. It allows users to extract data from different sources, manipulate the specified data into a form that matches their needs, and load it into Excel. Back in 2013, a specially created group of developers inside Microsoft released for Excel a free Power Query add-on (other names are Data Explorer, Get and Transform), which can do a lot of useful things for everyday work:
- Download data to Excel from almost 40 different sources, including databases like SQL, Oracle, Access, Teradata, corporate ERP-systems like SAP, Microsoft Dynamics, Internet services like Facebook, Google Analytics, and almost any site.
- Collect data from files of all basic data types like XLSX, TXT, CSV, JSON, and HTML, both singly and in bulk, from all the files in the specified folder. From Excel workbooks, you can automatically download data from all sheets at once.
- Clear the data from the “garbage”: extra columns or rows, repetitions, service information in the “header,” extra spaces or unprintable characters, etc.
- Putting data in order: correct case, number-as-text, fill in the blanks, add the correct table header, disassemble the text sticking to the columns and merge back, divide the data into components, etc.
- Transform tables in every way by bringing them into the desired view (filter, sort, change the order of columns, transpose, add totals, expand cross-tables into flat ones, and roll back).
- Substitute data from one table to another by the coincidence of one or several parameters, i.e., perfectly replaces the VPR function (VLOOKUP) and its analogs.
Power Query is found in two versions: as a separate add-in for Excel, which can be downloaded from the official Microsoft website, and as part of Excel 2016. In the first case, a separate tab appears in Excel after installation. In Excel 2016, all the Power Query functionality is already built-in by default and is located on the Data tab as a group of Get and Transform.
The functions, no matter the way you got, there are completely identical. The principal 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 (update query).
This is the most useful add-on for a wide range of users among all listed in this article. There are a lot of tasks for which previously you had to either terribly pervert with formulas or write macros – now they are easily and beautifully done in Power Query. Moreover, and with the subsequent automatic updating of results. And given the free, in terms of “price-quality” Power Query is simply out of competition and an absolute must-have for any average advanced Excel user these days.
Power Pivot is an in-memory data modeling component that provides highly compressed data storage and extremely fast aggregation and calculation. It is also available as part of Excel and can be used to create a data model in an Excel workbook. Power Pivot can load data by itself or can load data into Power Query. It is very similar to the SSAS (SQL Server Analysis Services) tabular model, which is similar to the server version of Power Pivot.
Power View is an interactive visualization tool that provides users with a drag-and-drop interface that allows them to quickly and easily create data visualization in their Excel workbooks (using the Power Pivot data model).
Power Pivot is also an add-in for Microsoft Excel but is intended a bit for other tasks. If Power Query is focused on importing and processing, then Power Pivot is needed mainly for the complex analysis of large amounts of data. In the first approximation, you can think of the Power Pivot as pumped pivot tables.
The general principles for working in Power Pivot are as follows:
- First, you load data into Power Pivot — 15 different sources are supported: common databases (SQL, Oracle, Access …), Excel files, text files, and data feeds. Besides, you can use Power Query as a data source, which makes analysis almost omnivorous.
- The links are configured between the loaded tables or, as they say, the Data Model is created. This will allow in the future to build reports on any fields from the existing tables as if it was a single table.
- If necessary, additional calculations are added to the Data Model with the help of calculated columns (an analog of a column with formulas in a smart table) and measures (an analog of a calculated field in summary). All this is written in a special internal Power Pivot language called DAX (Data Analysis eXpressions).
The reports of interest to us in the form of pivot tables and charts are built on the Excel sheet of the Data Model. Power Pivot has several features that make it a unique tool for some tasks:
- In Power Pivot, there is no limit on the number of lines (as in Excel). You can load tables of any size and work with them calmly.
- Power Pivot is very good at compressing data when loading it into the Model. A 50 MB source text file can easily turn into 3-5 MB after downloading.
- Since Power Pivot is “under the hood,” in fact, a full-fledged database engine, it copes with large amounts of information very quickly. Need to analyze 10-15 million records and build a consolidated? And all this on an old laptop? No problem!
Combine Power Query and Power Pivot
Power Query and Power Pivot complement each other. Power Query is the recommended tool for locating, connecting to and importing data. Power Pivot: Powerful data analysis and data modeling tools in Excel are very convenient for modeling the data you imported. You can also use it for data in Excel to view and visualize it using Power Map, Power View, Pivot Tables, and Pivot Charts, and then interact with the resulting workbook in SharePoint, on Power BI sites in Office 365, and in Power BI application Microsoft Store.
This add-on first appeared in 2013 and was originally called GeoFlow. It is intended for the visualization of geo-data, i.e., the numerical information on geographical maps. The source data for the 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 capabilities, by the way) can be downloaded completely free again from the Microsoft website. The full version is included in some Microsoft Office 2013-2016 packages with Power Pivot – as a 3D map button on the Insert tab (Insert – 3D-map):
Key features of the Power Map:
- Maps can be both flat and three-dimensional (globe).
- You can use several different types of visualization (histograms, bubble charts, heatmaps, fill areas).
- You can add a time dimension, i.e., animate the process and look at it in development.
Maps are loaded from the Bing Maps service, i.e., To view, you need very smart Internet access. Sometimes there are difficulties with the correct recognition of addresses because names in the data do not always coincide with Bing Maps.
In the full (not demo) version of Power Map, you can use your downloadable maps, for example, to visualize visitors to the shopping center or prices for apartments in a residential building directly on the construction plan.
Based on the created geo-visualizations, you can create videos directly in the Power Map (example), to share them later with those who do not have an add-on installed or include PowerPoint in the presentation.
This add-in appeared for the first time in Excel 2013 and is designed to “revitalize” your data – the construction of interactive graphs, charts, maps, and tables. Sometimes it uses the terms dashboard (dashboard) or scorecard (scorecard). The point 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 from your data from the Power Pivot Data Model. Here are the nuances:
- The source 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 – a Microsoft analog of Flash (free).
- On the Microsoft site, by the way, there is a very decent training course on Power View in Russian.
Power BI, Power Query, and Power Pivot are Related
Here is a simple diagram explaining how these Powerful tools are related:
Unlike previous ones, Power BI is not an add-in for Excel, but a separate product that represents a whole complex of tools for business analysis and visualization. Power BI is a SaaS service that allows business users to serve their own business intelligence needs. It provides built-in connectivity to SaaS services, such as Salesforce and many others. It provides connections to local and cloud sources using a combination of a direct request and periodic data updates. It is available as a freemium service. It is the successor to Power BI for Office 365, based on Microsoft Office 365 and SharePoint Online, and through Excel 2013 it includes Power Query, Power Pivot, and Power View.
Power BI (with O365 and SharePoint Online) provides a website where users can upload and share their created content with other users can manage gateways to a corporate data source, include data updates and advanced features such as Q and A, which allows natural language query data models. Microsoft also released the standalone Power BI Desktop application, which links Power Query, Power Pivot and Power View in a standalone application, eliminating the limitation of Excel 2013. It is also possible to achieve great Power BI functionality using local SQL Server 2012+, Excel 2010+, and SharePoint 2010+ in place if the cloud is not an option for you.
Power BI Desktop is a program for analyzing and visualizing data, which includes, among other things, all the functionality of Power Query and Power Pivot + add-ons and improved visualization mechanisms from Power View and Power Map. Download and install it for free from the Microsoft website. In Power BI Desktop, you can:
- Download data from more than 70 different sources (as in Power Query + additional connectors).
- Link tables to a model (as in Power Pivot)
- Add additional calculations to data using measures and calculated columns on a DAX (as in Power Pivot)
- To create beautiful interactive reports based on data with different types of visualizations (very similar to Power View, but even better and more Powerful).
- Publish the generated reports on the Power BI Service website (see the next paragraph) and share them with colleagues. And it is possible to give different rights (reading, editing) to different people.
How is Power BI Different from Excel?
So Power Query and Power Pivot in conjunction with Excel can create interactive reports. But there are several crucial differences between Power BI and Excel.
- Power BI allows rich, immersive, and interactive experiences out-of-box. You can click on a bar in a bar chart, and other visuals respond to the event and highlight or filter relevant data. You can show graphs and visuals that are very tricky (or impossible) to reproduce in Excel like maps, pictures, and custom visuals.
- Power BI works with large data sets. There is no artificial limit of 1mn rows in Power BI. You can hook up to a business data set and analyze any volume of data. The limit depends on what your computer (or Power BI server) can process.
- Share and read reports easily. You can create reports in Power BI and share them in formats that are universal (i.e., browser pages or apps). This means your boss need not have Excel or Power BI installed to enjoy the beautiful reports you create.
Power BI is for storytelling, while Excel is for almost anything. You can use Excel to simulate pendulum motion, calculate Venus orbit, model a start-up business plan, or many other things. Power BI is mainly for data analysis and storytelling. If you try to replicate a large, intricate financial model or optimization problem with Power BI, you will either fail or suffer miserably. On the other hand, if you use Power BI for making reports, and running cool analysis algorithms (clustering, outlier detection, geospatial patterns, etc.), you will wow your colleagues and bosses.
However, with excellence comes complexity. The more you can do with one tool, the more skill it takes to use it. In case you’re just starting with Power BI, you can easily get help by hiring a consultant to show you and your team the proverbial “ropes.” Power BI training services facilitate a smooth transition of the application to your in-house support teams and give them the skills to keep improving your reporting capabilities.
FluentPro Power BI Consulting Services
Our consultants specialize in Microsoft technologies to help your business reap the full benefits of your data. FluentPro’s Power BI Center of Excellence is a professional team of consultants and developers who will provide you with all the needed assistance to make the most out of your investment in the Microsoft Power BI platform. Our Power BI consulting services include:
- BI strategy and business analysis
- Data architecture and integration
- Data modeling, including DAX programming, Power Query ETL implementation, etc.
- Power BI visualization and reports
Our Power BI consulting team has seen a dramatic increase in demand for our services from businesses that want to hire our expertise to use new analytical tools. In addition to our core technologies, we can put together an integrated solution to meet all your requirements. Our Power BI consulting service is based on 10+ years of experience. We’ll define your BI strategy, architect and implement all the needed infrastructure, and turn your data into comprehensive insights to empower decision-making. You should consider FluentPro Power BI consulting services to help you with:
- Adoption and implementation of Microsoft BI technologies
- Customizing the Power BI platform to your maximum benefit
- Integrating any data sources and maintaining data infrastructure
- Generate business value through data analytics and visualization
- Get an individual approach to each client, no matter the size or the industry of your enterprise
- Get advanced customization that is tailor-made for your team and its needs
- Have help with a personal approach to the key people in the organization that want to get started with Power BI.
- Work with people who are available from anywhere in the world.
- Have necessary training delivered to the key Power BI business users so they can learn the skills needed to be successful.
Schedule a free consultation
to find out how Power BI consulting team can help