We explain.
The terms “Power Query,” “Power Pivot,” “Power BI,” and other “Power” terms 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 clear up the 'Power' confusion.
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, almost any sites.
Collect data from files of all basic data types like XLSX, TXT, CSV, JSON, 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 rollback).
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 analogues.
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 since version 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 get 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 modelling component providing highly compressed data storage & 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 visualisation tool that provides users with a drag and drop interface that allows them to quickly and easily create data visualisation in their Excel workbooks (using the Power Pivot data model).
Power Pivot is also an add-in for Microsoft Excel but intended a bit for other tasks. If Power Query is focused on importing and processing, then Power Pivot is needed mainly for 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, 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 analogue of a column with formulas in a smart table) and measures (an analogue 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 analyse 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 modelling tools in Excel are very convenient for modelling the data you imported. You can also use it for data in Excel to view and visualise 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.
Power BI, Power Query, and Power Pivot are Related.
Here is a simple diagram explaining how these Powerful tools are related:
Power BI
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 visualisation.
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 analysing and visualising data, which includes, among other things, all the functionality of Power Query and Power Pivot + add-ons and improved visualisation 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 visualisations (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?
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 analyse 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 planetary orbits, 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 optimisation problem with Power BI, you will either fail or suffer miserably. On the other hand, if you use Power BI for making reports, 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.
New to data analytics? See our Power BI Foundations one-day course.
Comdex Training Centre 2022
Comments