Difference between PowerPivot, PowerQuery and PowerBI [closed]

Asked 17/4, 2015 at 10:1 Answered 6/9, 2021 at 6:9

What is the difference between

What are the use cases for each tool? What are the limitations? What requirements must be met to use the tool?

Cinder answered 17/4, 2015 at 10:1 Comment(1)

Reading PowerPivot it sounds that it is similar to Power Pivot for Sharepoint but powerpivot is a part of the Microsoft Business Intelligence suite and can be used indepentently from Power Pivot for Sharepoint. What does the End of Life for the sharepoint "version" mean for the powerpivot Business suite product? – Accretion 21/12, 2022 at 10:37

140

Power Query

Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in. It allows users to pull data from various sources, manipulate said data into a form that suits their needs, and load it into Excel.

Power Pivot

Power Pivot is an in-memory data modelling component that enables 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 within an Excel workbook. Power Pivot can load data itself, or can have data loaded into it by Power Query. It is extremely similar to the SSAS (SQL Server Analysis Services) Tabular model, which is like a server-based version of Power Pivot.

Power View

Power View is an interactive visualisation tool which provides users with a drag-and-drop interface allowing them to build quick and easy visualisations of the data in their Excel workbooks (using the Power Pivot data model).

Power BI

Power BI is a SaaS service which enables business users to service their own business intelligence needs. It provides built-in ability to connect to SaaS services like Salesforce and many others. It provides connectivity to on-premises and cloud sources using a combination of direct query and periodic data refreshes. It is available as a freemium service. It is the successor to 'Power BI for Office 365' that was based on Microsoft's Office 365 and SharePoint Online products, and, through Excel 2013, encompasses Power Query, Power Pivot, and Power View.

Power BI (with O365 and SharePoint Online) provides a site where users can upload and share their created content with other users, as well as manage gateways into enterprise data source, enable data refresh, and advanced features like Q&A, which allows natural language querying of data models.

Microsoft has also released a standalone Power BI Desktop application, which ties together Power Query, Power Pivot, and Power View in a standalone application, removing the Excel 2013 constraint. Power BI Desktop is available for free.

It is also possible to achieve a lot of the functionality of Power BI using on-premise SQL Server 2012+, Excel 2010+ and SharePoint 2010+, if cloud-based is not an option for you.

Midcourse answered 24/4, 2015 at 15:8 Comment(3)

There is also now PowerBI Embedded which allows embedding of PowerBI reports in web applications without the need for PowerBI accounts for end users – Gaylord 2/8, 2016 at 20:54

Microsoft have now released on On Premise Server version of Power BI. This gives the ability to have complete sovereignty over your data. It works pretty wll but does not make possible Natural Language Query. – Scour 31/1, 2018 at 1:7

Excel no longer requires an addon to use power query, it's built in. Some versions call it get data. – Dinahdinan 28/2, 2021 at 14:12

Simple answer:

PowerBI=PowerQuery+PowerPivot+PowerView

Alternate answer:

If 

[PowerQuery]="Clean Data/M language" and [PowerPivot]="Calculations/DAX language" and [PowerView]="Data visualization"

then 

[PowerBI]=concatenate ("PowerQuery","-","PowerPivot","-","PowerView")

else "Excel"

Hope this answers the question :)

Student answered 12/2, 2018 at 2:55 Comment(0)

Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. Power View is a feature of Microsoft Excel 2013, and of Microsoft SharePoint Server 2010 and 2013 as part of the SQL Server 2012 Service Pack 1 Reporting Services Add-in for Microsoft SharePoint Server Enterprise Edition.

Power Map for Excel is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. A power map lets you discover insights you might not see in traditional two-dimensional (2-D) tables and charts.

Protasis answered 4/5, 2016 at 14:4 Comment(2)

According to my course, Power Map visualizes geospatial data from a PowerPivot Data Model. That does not sound as the same. Can you clarify? – Rudelson 22/2, 2017 at 11:45

Both Power Map & Power View are based on a Power Pivot data model, but they're not the same. Power View is not limited to maps but does not have 3D maps. Note: Power View is pretty much deprecated in Excel 2016 & higher and is no longer in active development. – Julesjuley 10/4, 2017 at 19:10

I like both answers from GShenanigan (tools breadown) and excelislife (simple answer). But I will merge them all into my own synopsis to provide an updated definition as of 2018 November.

What is Power BI?

Simple answer: PowerBI=PowerQuery+PowerPivot+PowerView

If 
[PowerQuery]="Clean Data/M language" and [PowerPivot]="Calculations/DAX language" and [PowerView]="Data visualization"
then
[PowerBI]=concatenate ("PowerQuery","-","PowerPivot","-","PowerView")
else "Excel"

Another perspective of the PBI Tools

I have removed Power View, because I do not think this is relevant anymore. It was a canvas sheet for building visualizations (charts, maps, graphs, table), but now this is done in PBI Desktop.

Power Query

Users connect to a data source and build a data set. Drag-and-Drop interface to build ETL steps with the ability to also write custom code (Language: M). Data is loaded to Excel table or PowerPivot data model. NOTE: There are a variety of “connectors” for accessing many different data sources. To name a few: Database tables (SQL Server/Oracle/SSAS...), ODATA Feed, SaaS services (Salesforce/Exchange/…), Files (text/csv), and more.

Power Pivot

Users import data sets and build a data model. Measures are Calculations/Aggregations (Language: DAX). Report visuals are built off of the data model (DATASETS + RELATIONSHIPS + MEASURES) using the PowerBI visualizations canvas.

Power BI

PowerBI Desktop, or Excel, is where you build the reports (charts, maps, graphs, table). PowerBI uses the PQ and PP steps to get the data.

There are few ways to share these reports as seen on this blog post, "PBI 15 ways to share", https://blog.coeo.com/power-bi-15-ways-to-share-data. Here are the most relevant for enterprise collaboration based upon my experience.

PBI Service  Deploy Power BI reports to the service, https://app.powerbi.com/. These are overarching visualizations of a business overview, created by pinning visualizations from one or many reports. You publish dashboards inside and outside your organisation by hitting the share button when viewing the dashboard. Recipients of the share require a Pro licence (you pay per user per Month), or you deploy the content to premium capacity. You can configure the ability for internal recipients to re-share during the sharing process. Re-sharing can also be turned off completely in the Power BI admin portal.

On-Premises Report Server  Deploy Power BI reports alongside paginated SSRS reports.

Embed - App Owns Data  Embedded within third-party applications, requires no authentication by the end-user to Power BI.

Share PBIX  The PBIX file is open by the users in the development environment (PBI Desktop). On-premises and free, no PBI Pro license required.

Export to Excel or CSV  Click the ellipses on the top right of a visualization and select “export data”.

Hope this helps!

Sergius answered 14/11, 2018 at 20:17 Comment(0)

Power BI: Power BI is a Business Intelligence and Data Visualization tool which helps you to convert data from the various data source into interactive dashboards and BI reports. It provides multiple software connectors and services.

Power Query: It allows you to discover, access, and consolidate information from different sources.

Power Pivot: A modelling tool.

Power View: It is a presentation tool for creating charts, tables, and more.

Power Map: Helps you to create geospatial representations of your data.

Power Q&A: This allows you to use natural language to get answers to questions.

Stability answered 19/4, 2021 at 9:52 Comment(0)

-1

power bi is a visualization and business intelligence platform that can run power query and power pivot inside.

power query basically helps you to get data from different sources and automatize.

power pivot is a data modeling tool

Widera answered 6/9, 2021 at 6:9 Comment(0)

Another perspective of the PBI Tools

Recommended topics

Hot tags