What is the difference between PowerPivot, PowerQuery and PowerBI? How should one decided which tool should be used and when.
Power Query is the recommended experience for importing data. Power Pivot is great for modeling the data you've imported. Use both to shape your data in Excel so you can explore and visualize it in PivotTables, PivotCharts, and Power BI.
The normal pivot version just lists fields within this single table or source that we're pointing to. Power Pivot allows us to access any of the fields in any of the tables in our data model, and then analyze them based on any relationships that we've defined. So that's the most obvious of the differences.
Power Pivot is an part of Power BI. So there is no need to get Power Pivot. It is always there. You can use various features of Power Pivot from Modeling ribbon and from data & relationship views.
Power Query in Power BI provides very effective functionality to pivot and unpivot columns. For the pivot functionality you turn rows to columns and for the unpivot functionality the inverse is true where columns are transformed into rows.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With