From the course: Excel: Working Together with Power Query and Power Pivot

How Power Query and Power Pivot work together

From the course: Excel: Working Together with Power Query and Power Pivot

Start my 1-month free trial

How Power Query and Power Pivot work together

“

- Previously just a simple spreadsheeting product, Excel now provides a seamless and complete end-to-end solution from source data to actionable insights via the generation of powerful reports. Now, in this course, we're going to specifically look at two amazing features, Power Query and Power Pivot. They're designed to complement each other. But before we take a look at how this happens, let's just take a step back and look at these two features separately. Firstly, let's take a look at Power Query, which is also known as Get and Transform. Power Query is a data discovery tool. We can take source data from a huge range of both static and live data feeds, and we can then massage it into any view we may choose. We can merge different data sources together. We can extract custom views, we can even Unpivot data, and then go ahead and share our data with other users and all of this, using a code-free process known as Queries. Now let's go ahead and take a look at Power Pivot. Power Pivot is a fantastic way of enhancing your experience through something called a data model. A data model allows you to establish links between tables, just like a relational database. We can also create custom calculations, define KPIs, and then evaluate our experience using Excel's native Pivot Tables and Pivot Charts, and all of this without having to write complex formulas and look ups. So how does Power Query and Power Pivot work together then, you might be wondering. Well, it's as simple as this. Power Query is a data-staging process, or in most simple terms, getting your data ready for action. Power Pivot then consumes the data you've prepared and enhances it further, thus preparing it for analysis using Excel's standard features. And then the fun begins. As you start to analyze your data, you will no doubt come across several scenarios that will impact your work. This will include things such as new source data, changes to a query, adding a relationship and that kind of thing. If you've set your work up correctly, making these changes is a breeze and will be dynamically and automatically reflected throughout your work. And all this without having to perform any manual changes such as copying and pasting. Now I'm sure you're really keen to get started with all of this. So let's go ahead and get straight into it.

Contents