Connecting PowerQuery Data to the Data Model

Private: PowerQuery for Finance Teams Connecting PowerQuery Data to the Data Model

Connecting PowerQuery Data to the Data Model

Previous Session Summary

The last lesson was pretty brisk as we looked at how to get the Power Pivot add in in to Excel. However the lesson before that, we looked at the various ways to load data from the PowerQuery Editor into Standard Excel. These included loading data to tables, pivot tables and pivot charts or not loading the data at all.

Lesson Synopsis

In this lesson we’re going to look at how to add PowerQuery data to the Data Model or PowerPivot. Now I love PowerQuery and use it pretty much every day, but for doing any sort of serious analysis work…well you want PowerPivot.

PivotTables are great tools, but they do have their drawbacks and PowerPivot addresses pretty much all of them.

When you combine PowerQuery, an awesome tool that grabs, consolidates and puts data into a columnar format, with a tool that is just built for analysing huge quantities of data…well it’s a good mix!

Let’s take a look.

Resource Listing

The Video

Play Video

Lesson Notes

Right well let’s start off at the beginning. What is PowerPivot?

What is PowerPivot?

PowerPivot is an Excel add in that supercharges pivot tables. They summarise data like PivotTables do but you can do four important things that you can’t do with normal PivotTables:

1. You can connect a single PivotTable to a variety of data sources. Compare this to than only being able to perform PivotTables on data that you already have formatted in a single Excel worksheet usually using VLOOKUPS to bring information from supporting datasets into a main dataset.
2. You can do work on a shedload more data.
  In a normal PivotTable, if you have a sufficiently large dataset (maybe getting into the hundreds of thousands of rows and plenty of columns, probably some of them calculated) you will eventually crash your computer or at least make it work really slowly. When we’re trying to create a dashboard or platform that other people can use and use easily, then asking them to wait 20 secs plus before they get their information does not go down well. And if you introduce the possibility that it might crash their computer? Well that just means that they’ll never look at the work that you do, even if it’s absolutely brilliant.
  PowerPivot is designed to analyse millions of rows of data so this just isn’t a problem.
3. You can compare multiple datasets in a single pivot table.
  In a normal PivotTable, you have to have all the information for the PivotTable in a single table. So what do you do if you want to compare two or three datasets, say sales from 2019, 2020 and 2021? Bring all the data into a single table? That could be a pretty hefty table before you start worrying about matching up things like dates across multiple years, not to mention time consuming to do it!
  This is no problem for PowerPivot though as you can connect the data model to all the datasets at once and include and compare information from all datasets against each other in a single PivotTable.
4. You have the opportunity to add calculations into the PivotTable. And yes, this is something that you can do in standard Excel through the Calculated Fields option, but you have SO many more options in Powerpivot and it works better too. You can add in calculated fields into a dataset or add in custom aggregation functions like SUMIFs (or calculations that are a whole lot more complex!). In fact PowerPivot has its own code called DAX which we could do a whole other course on – but don’t panic as at least in the early stages, it’s very similar to the code that you write in standard Excel.
How do we load data into the data model?

Ok, it’s pretty simple to load data into the data model.

First of all, we need to load the ‘Load To’ menu. To do this, we go to the Queries and Connections pane, right click the query that we want to load into the data model, and select ‘Load To’.

At the bottom of this menu we have a tick box labelled, ‘Add this data to the data model’. That’s the one we need so tick that one and click ok.

Now Excel will ask if it’s ok to remove the table of data that you’ve got loaded in to put it into PowerPivot. Click ok.

You can then go into the PowerPivot tab, select ‘Manage’. Once you’ve done that then you’ll be taken into the PowerPivot Data Model.

In the Data Model, you’l see tabs along the bottom of the screen, just as in standard Excel. There should only be one in your Data Model screen and it will be named the same name as your query.

.... and a little more!

PowerPivot – You don’t have to use PowerQuery to put data into the Data Model. PowerPivot has its own connectors and you can connect to information held in the same workbook as the data model, or in a separate one.

Previous Lesson

Back to Course

Next Lesson