What is what – Power Query, Power Pivot, Power View – Trainings, consultancy, tutorials

What is what – Power Query, Power Pivot, Power View

This article describes the basic components of Power BI in Excel and defines what does they do.

We are talking about Power BI in Excel – not Power BI in a separate online or desktop application.

In one sentence

Power Query can be used for getting data to Excel, Power Pivot manages the data stored in Data Model and Power View presents the data in reports.

Power Query

Power Query (form Excel 2016 named Get and Transform) can not only download data to Excel, but make nice transformations as well. For example filter, calculate, merge multiple sources etc. etc.

Power Query doesn’t really store data, just handle their flow – it is ETL software.

Inputs for Power Query

Table in excel file we are working with
Table in any other excel file
Various databases
Sources like webpages, Facebook etc.

Outputs

Table in excel file we are working with
Pivot table
Connection used for importing data to Data Model.

Power Pivot

Power Pivot manages data in Data Model, which basically is a data warehouse. Data in Data Model are physically stored in xlsx file, but not in sheets and cells.

Inputs for Data Model

Table in excel file we are working with
Table in any other excel file
Various databases
Connection created with Power Query

Basically, you can connect Power Query to external or internal source directly with or without Power Query. It only has to be used for more complex connections.

Outputs of Data Model (of Power Pivot)

… are quite limited.

Pivot table in the same Excel file
Normal (non-pivot) table (with some workaround)
Report created in Power View
Some technologies able to connect to Power Pivot (like Tableau)

Power View

Power view enables you to create nice report, containing images, maps etc.

Inputs for Power View

Table in excel file we are working with
Data Model

Outputs of Power Query

Power Query report