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.

More tutorials related to Power Query

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)

More tutorial s related to Power Pivot

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

More tutorials related to Power View.