Power Query: Relationship Goals

Welcome to our new Power Query blog. Today I look at the relationship between Power Query and Power Pivot.

So far I have concentrated on how to use Power Query as a standalone tool. Next week’s blog will look at extracting data from a table and loading to the Excel model. This will allow the data to be manipulated by another Excel Add-in: Power Pivot. Therefore today I will take a look at the relationship between Power Query and Power Pivot.

Over the last few blogs I have shown that Power Query is a useful ETL (Extract, Transform and Load) tool. Well thought out features allow data to be filtered, manipulated and merged. In addition to the previous examples where I have loaded data into an Excel worksheet, loading to the Excel model allows further modelling and analysis by Power Pivot.

I do not plan to go into the detail here of how to install Power Pivot, as Power Query is the star of this blog. Suffice to say that if Power Query has been installed, then Power Pivot will either be available to install from the Microsoft website or already be in place, in which case it can be enabled as a COM add-in (this is slightly more convoluted than you usual, run-of-the-mill add-in: File -> Options -> Add-Ins and then from the Manage drop-down select ‘COM Add-Ins’ and then press the ‘Go…’ button):

Power Pivot can fine tune the Excel Model and then perform calculations so that the data is ready to be displayed in PivotTables, charts or grids, or in a visualisation tool like Power View. Power Pivot can create relationships between tables, and create formulas and KPI’s (Key Performance Indicators).  Power Pivot refines and builds on the Excel model created by Power Query.  In the next blog, I will describe how to extract a table from a database, transform the data, and load it into the Excel model ready for further analysis using Power Pivot.  Watch this space!

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.  Come back next time for more ways to use Power Query!