Power Query, Power Pivot and Power BI – What’s the Difference?

Last modified date:

Best way to understanding of the differences between individual Power BI tools is to realize that every tool has some role in the whole processing of the final report.

In standard MS Business intelligence we have tools by which we ensure data transformations (SSIS), processing of data model with data storage in relation structure (SQL Server) and tool for report producing (SSRS, SSAS). That is exactly the role of Power Query, Power Pivot and Power BI.

All these tools belong to the group of Self-service tools designed for common users.

Power Query – Tool of The Future For Self-Service Business Intelligence in Excel

Power Query is an excel add-on. It ensures ETL processes (Extract, Transform, Load). It also enables user to extract data from various sources (more in article on Power Query).

You can load the data into Excel application and then easily clean the data and transform them into a form suitable for reporting or loading into data model.

The biggest upsides of Power Query:

  1. It is free (also Power Pivot and Power BI)
  2. It is used as an add-on of MS Excel (also for Power Pivot and Power BI)
  3. Enables automatic connection to various data sources and it is possible to update data regularly
  4. It is possible to easily transform the data after connecting. Power query remembers all the changes and if the data source is changed it automatically repeats all the required steps by refresh.

Powerpivot – Create Data Model in Excel Just Like a Database Professional

Power Pivot is available as an Excel add-on. It is used mainly to create data model in Excel. Its biggest strength lies in combination with Power Query but you can use it even without it.

It is very similar to SSAS (SQL Server Analysis Services) and it makes it possible to save data in compressed form. Extremely fast aggregation with calculation is also made possible. You can use DAX language for queries in Powerpivot in a same way as you can use special language MDX in SSAS.

Power Pivot can be used to create relation model connected to other Excel sheets and to create data hierarchies where the data can be simply displayed and updated using pivot table.

Powerpivot model

Simple data model consisting of financial balance sheet connected to calendar dimension. This dimension can be of service to create time hierarchy (e.g. Year – Month – Day) in the final contingent table. (Czech language only for now)

Power Query, PowerPivot a Power BI

The result of the relation model created by Power Pivot is a financial balance sheet nicely displayed in pivot table. (Czech language only for now)

Biggest upsides of Power Pivot:

  1. Creation of functioning relation model which was previously possible only in paid tools
  2. It is possible to combine it with Power Query and create automatic system for loading and reporting of data
  3. Possibility to report through contingent tables
  4. it is possible to work with the data via DAX formulas

Power BI – All Advantages In One Tool

I wrote on Power BI in several articles, e.g.:

Whatever you can do using Power Query and Power Pivot in Excel can also be achieved in Power BI. It has all the tools integrated in itself plus it serves as a cloud based service. By creating Microsoft power BI account you open for yourself the doors to unseen possibilities.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: Don't Miss Out Power BI reporting Power Pivot Power Query

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.