Excel, the Power Query Editor, and Power Pivot – Having your cake and eating it too!

Jessica Jolly

0 comments

Most people think of Power Query Editor in terms of Power BI, and even power Excel users are not familiar with Power Pivot. But you can use the Power Query Editor just as easily with Excel, and it can save you a heck of a lot of time! If you use pivot tables (and these days, who doesn’t?), Power Pivot can really ‘up’ your game.

Why did I volunteer to create a talk and write this e-book? Because I find that most people think of Power Query Editor in terms of Power BI, and even power Excel users are not familiar with Power Pivot. But you can use the Power Query Editor just as easily with Excel, and it can save you a heck of a lot of time! And if you use pivot tables (and these days, who doesn’t?), Power Pivot can really ‘up’ your game. So in this ebook, I hope to introduce you to the wonders of using the Power Query Editor and Power Pivot with Excel.

A note on acronyms: for convenience, you will frequently see Power Query Editor abbreviated as “PQE” throughout this ebook.

The Basics

Let me frame out the basic structure first. If you have Excel 2016, you automatically have Power Pivot. (You have to activate it to see the tab in Excel, but don’t worry, we will cover that.) If you have Excel 2013 or Excel 2010, you can get access to Power Pivot, but it isn’t built into the tool.

You can access the PQE through Excel. PQE isn’t an independent application that you download separately. (This is very similar to using PQE with Power BI—you can’t access it independently.)

Power Query Editor

The PQE is a tool you can use to process raw data to prepare it for use in more complex Excel usages. It can save hours of labour because it will track the changes you make (almost like a macro does), and you can re-apply those steps to another, similar file with data in it.

Power Pivot

Power Pivot is an extension of the pivot table capabilities of Excel. It leverages the concept of a data model to allow you to build a pivot table with multiple worksheets (pivot tables in Excel limit you to just one worksheet per pivot table).

As the diagram above implies, you can use each of these tools independently, or together in different combinations. We are going to examine each of these tools in this e-book, starting with the Power Query Editor.

Accessing the Power Query Editor

Where do you find the PQE? It is easiest to start from the vantage point of selecting a data file, so we will start that way.

  • In Excel, Go to the Data tab
  • Choose Get Data
  • Select your data source

Once you have selected the Excel file you want to work with, you will see the following window, called the Navigator Pane:

  • Then choose Transform to open the PQE

Select the worksheets you want to work with

Make sure you check the “multiple items” box

When you choose Transform Data, it will take you to the Power Query Editor.

Don’t worry that you don’t see all of your data in the Navigator Pane. It is just showing you a small sample of the data you are selecting.

Once you are in the PQE, you will see this screen:

The PQE is a ribbon-based tool, just like Excel, Word, PowerPoint, and Word.

These are the worksheets (or tables) you have imported. They are called Queries

Here are the steps you take, each recorded. You can re-arrange them, delete them, and add to them. It is almost like a macro in its’ functionality.

This panel describes the contents of the query you have selected on the left-hand side.

Even though it looks like Excel, it isn’t. You can’t double click in a cell and edit the contents.

While you cannot change a specific data point in a cell (as you can in Excel), you can re-shape your data.

Shaping your data

Removing rows and columns. Sometimes the dataset you want to work with has data that you don’t need to analyze. If you know that you won’t ever need that data, you can delete it from your query as a column and/or a row. Removing a column will take that field out of your data permanently, for all the rows of your data. Removing one or more rows will remove those specific records.

Unlike in Excel, you can’t remove rows in a discontinuous pattern. As the above picture shows, you can remove rows at the top or bottom of the dataset, or alternate rows.

If I chose the Remove Other Columns option, all the columns except the two columns I have selected will be deleted.

You can remove individual columns, or if you have a large dataset with just a few columns you want to keep, you can choose the columns you want to keep and remove the others. (Talk about a handy feature!)

Promoting rows. It is really important to have header rows. You should always have them in your Excel files (there are a lot of features that don’t work correctly if you don’t have them), but sometimes, despite your best efforts, they aren’t always there.

In this screenshot, you can see that the column headers are missing.

No worries! You can take the first row and promote it to be the header. In fact, you have to do that because you can’t simply type in headers.

On the Home tab, select Use First Row as Headers.

These are just a few of the transformations you can perform in the Power Query Editor. Think of your data as a raw block of marble (or other material). You use the Power Query Editor to chisel away at the unnecessary data until you have just what you need for your analysis.

This is not a manual on how to use the Power Query Editor, so I can’t go through all the possible transformations. But you should. There are a number of excellent resources on how to use the Power Query Editor, including books and websites.

Keeping track of your transformations

No matter what the steps are that you take, Power Query Editor makes it easier to review the changes you have made, rearrange them, or delete a transformation if it didn’t work the way you expected.

The steps that you take are listed in the Applied Steps box.

The Applied Steps box records every step you take (kind of like that song by the Police). You can re-arrange the steps or delete a step. (Be aware that when you re-arrange or delete steps, you have to be careful that you are not deleting a step that another step depends on. Similarly, if you re-arrange steps, be sure that you don’t put a step that depends on another step before the dependent step. I realize that is confusing—it’s kind of like putting the letter F before the letter C—you get the idea.)

M language

If you look carefully at the previous screenshots, you will see that they all have one thing in common: the formula bar. Every step that you take is listed in the Formula Bar, in a language called “M.”

Here are some examples:
Promoted Headers = Table.PromoteHeaders(#”Changed Type”, [PromoteAllScalars=true])
Removed Columns= = Table.RemoveColumns(#”Changed Type1″,{“Region”})

The verbiage that is bolded and in red is in the M language. When you choose a step through the ribbon, the step is ‘translated’ into M. Don’t worry, you will not have to learn the M language to be effective. But if you do want to learn the M language, the easiest way to do so is to continue to choose your steps using the ribbon. Then you can access the Advanced Editor to see the steps in M.

Figure 1. The Advanced Editor window

Here you can see the M script for the steps. Here are the basic rules:

The script always starts with a LET statement
Followed by a SOURCE statement
Each step is preceded by a # symbol
All the steps, except the last one, ending with a comma
The last statement is an IN statement.

You can copy and paste M statements from one file to another, in order to perform the same steps more efficiently. You can copy the statements in the Advanced Editor and then open a Notepad application of some type and paste the statements into that.

Here you can see the M script for the steps. Here are the basic rules:

The script always starts with a LET statement
Followed by a SOURCE statement
Each step is preceded by a # symbol
All the steps, except the last one, ending with a comma
The last statement is an IN statement.

Then you open up a file with the same structure, paste the M statements into that file’s Advanced Editor, and you are most of the way home. Here are some things to make sure of:

  • The file that you are pasting the steps into must be the same structure, or almost identical, to the one where you created those steps.
  • You must change the Source= statement to ensure that it reflects the name of the new file.
  • You must verify that the name of the worksheet you are working on is the same as the worksheet in the new file.

You can make these changes by copying and pasting the changes you need to make, or simply deleting and adding the appropriate text.

You can also write your own M script if you are comfortable doing so, but it is far easier to make the changes you want through the ribbon, and then modify the script as needed.

More Transformations

Before we started talking about the Advanced Editor and M, we were talking about the types of transformations you can make. Let’s return to transformations to see some of the more interesting ones. We have a knotty problem in this file:

We have the first column, containing the name of the companies, and the second column containing the company ID. The remaining columns are for each week, with each company’s number of incidents for that week.

The problem is that this is human readable, but not machine-readable. For Power BI or Excel or Power Pivot to be able to make sense of this data, it needs to be in a tabular format, which means each company has a row for each week.

In order to do this manually, we would have to stand on our heads and spit wooden nickels (just an old American expression to indicate something difficult!). But with the Power Query Editor, there is an easier option: you can use the unpivoting option (because, in essence, the data is pivoted). Let’s do this!

First, we need to promote the first row to get it out of the way and to have ‘real’ column headers. Remember—you always need to have meaningful column headers (i.e. not “column1”, “column2” as shown below).

So we can use the command Use First Row as Headers on the Home tab to promote the first row.

(By the way, the same command is available on the Transform tab.) Once we do that, we will see this:

So far, so good. But, we still haven’t re-arranged our data into a tabular form. Let’s do that now.

Once you have selected the first two columns, make sure you are on the Transform tab, then select Unpivot Other Columns. (You may have to experiment with this technique, depending on your dataset, but if the experiment results in something unexpected or undesirable, remember that you can always undo what you did by clicking the X next to the step in the Applied Steps box.

Select the first two columns, just as you would in Excel.

So go ahead and experiment! (No harm, no foul.)

Also note that the Applied Steps panel has recorded this step. So as I said above, feel free to experiment. You can always delete it!

We now have the data in a tabular format: one row for each week of the year, by corporation. Power BI can handle the data in this format (although you will probably want to change the name of the “Attribute” column to “Week Ending”, or something more meaningful. You can do that by double-clicking into the column header and typing in a new name—just like you would in Excel.)

Now we can complete the circle and bring it back into Excel.

On the Home tab, you will find the Close & Load option. (I explain terminology later in the ebook, so hang tight if the term Load confuses you.)

We are going to choose Close and Load because that will take the data we have transformed and bring it back into Excel. We will talk about Close and Load To later.

Once you choose Close and Load, you will probably see this interim message:

Figure 20: The data is loading into Excel

(I say “probably” because I don’t always see it.)

Once the data is back in Excel, it will be a table, and the formatting will reflect that. It will come in as a new worksheet. Your original data is preserved on its’ worksheet.

The original data is still there, unchanged.

The table has the name of the original worksheet.

The transformed data comes in on a new worksheet, as a table.

Toggling back and forth between Excel and the Power Query Editor

You would think that getting back and forth between the Excel window and the Power Query Editor, was easy right? Nope.

This is Excel, on the left.

This is Power Query Editor, on the right

You can’t get back to the Excel file until you have closed the Power Query Editor. It’s not a big deal—more of a heads up, because it is not the usual Windows behaviour.

A detour: what is a table in Excel?

I just brought up the concept of a ‘table’ in Excel, and if you notice, I wrote (see above) that “…the formatting reflects the fact that it is a table.” But just because data looks like a table in Excel does not mean that it is a table. A table is a formal object that “knows” its boundaries, where it starts and ends. How does it do this?

When you invoke the Insert Table command in Excel (in Figure 24, above), Excel scans the data from top to bottom and from left to right.

It is looking for any all blank row or column. As soon as it “sees” an all blank row or column, it stops, thinking the dataset is complete.

For this reason, you should never have an all blank row or column in a dataset. I know people use blank columns and blank rows for formatting purposes, but this is a bad habit. There are lots of other ways to format a dataset without resorting to this poor practice. (And just a note, you can have a mostly empty row or column. Just not an all blank row or column.)

Once Excel finds an all blank row or column, it determines that this is where the data ends.

In this case, the data set is short: it only extends from A1 to K22. If data were missing from the selection, you would likely catch it. However, what if your data extends across many columns, and thousands of rows (not an unlikely scenario at all, you have to admit)? You may not catch that you are missing data, and your resulting analysis will be incomplete. This would be embarrassing at the least and damaging to your company (and to your reputation) at the worst.

So, be safe, not sorry. Never include any all blank rows or all blank columns in your datasets.

Before we leave the subject of tables, let’s take a minute to talk about formatting. Do you know the adage “clothes do not make the man”? Well, formatting does not make a table. There are a few ways to tell that you have a true table:

  • When you scroll down, the headers of the columns move up to the column label area.
  • The Table Tools contextual menu appears.

(The other way to be sure you have a table is to enter a formula in one of the columns. In a table, it will automatically copy all the way down the column.)

Ok, enough on tables. Back to our regularly scheduled programming.

Terminology

Now is as good a time as any to take a break and talk about terminology. It can be very confusing, especially for users (like me) who are coming from an Excel background.

Transformation: You may have noticed that we call changes to the data in the Power Query Editor “transformations” even though we would normally think of them as “edits”, and the tool itself is called the Power Query Editor. The term transformation comes from the database world. You may have seen or heard the acronym ETL, which stands for Extract, Transform, Load.

I like to think of transformations as sculpting changes you make. You can’t click into a cell in the PQE and edit the cell contents directly. But you can make changes to an entire column or row. From me, the sculpting analogy works. Feel free to substitute whatever analogy works for you.

The bottom line is that a transformation is a change to the dataset, but you can’t make it at the individual cell level, as you can in Excel.

Extract: Back to the ETL acronym. The extract describes the process of getting the data from another source. You extract the data from an Excel workbook, or an SQL database, or a website.

Import: Once you have extracted your data from its’ source, you have to put it somewhere. That process is called importing the data.

Load: You may remember I referred to the term load when we discussed the command Close and Load. What exactly are you loading and where are you loading it to? Well, you are loading the data you have extracted (from another source) and transformed (maybe only minimally or extensively). You have to have a destination location, such as Excel or Power BI. The Power Query Editor is only a way station, not a permanent destination.

Whether you are loading to Excel or Power BI (and we are focusing on Excel in this e-book), you are loading to the Data Model. The loading process from the Power Query Editor creates a data model, whether you choose Power BI or Excel as your destination.

Data model: I like to think of the data model as a container for your data. Remember that you may be bringing in data from a variety of sources and mashing them together. There has to be a way of relating these disparate data to each other and structuring them in their new arrangements. That’s called the data model.

A data model consists of tables and relationships between tables. You can think of a table as the rough equivalent of a table in Excel. A table is a set of cohesive data (data that “belongs” together), such as sales transactions, or product information, or customer information.

Relationship: You are probably very familiar with VLOOKUP (or indeed any of the LOOKUP functions) in Excel. To understand relationships between tables, think VLOOKUP without having the inconvenience of bringing over all the data.

A relationship is a connection between two tables in a data model, indicating which fields connect together. For example, you could have a product category field in a Product table, and you could also have a product category field in a Sales table. You want to be efficient though, and not have to bring over all the product category fields to the Sales table when you want to run a report with a little more detail about the product sold. So you create a relationship between these two tables (Sales and Product Category). That way, when you want to get more details in your report about the category, you use the relationship between Sales and Product Category to add details that are in the Product Category table on your report.

Again, if this sounds confusing, go back to your experience using VLOOKUP. That’s essentially what you are doing when you create a relationship between tables, but you are not bringing the actual data over from one table to another. The relationship makes the data in one table available to another table, without repeating the data in both places.

There are three types of relationships possible:

  • Many to One/One to Many: This type of relationship indicates that there is one record in one table that corresponds to multiple records in another table. A good example of this would be a Sales table, with multiple sales transactions with the same customer. In the Customer table, there would be one record for the customer. This would be a one (Customer table) to many (Sales table) relationship. This is the most common type of relationship in data models.
  • One to one: This type of relationship indicates that there is one record in one table that corresponds to one record in another table. One to one relationships are less common in data models, but you will encounter them.
  • Many to many: This type of relationship indicates that there are multiple records in one table that correspond to multiple records in another table. This is not a very common relationship type in most data models.

Query: When you bring data into the Power Query Editor, the data you bring in is called a query. If you were bringing in data from a worksheet into the PQE, it will be called a query, not a worksheet. The same is true for data you bring in from an SQL database (for example).

Power Pivot

It is hard to talk about the Power Query Editor without also covering Power Pivot, which is a close cousin. Power Pivot is an add-in to Excel that enables you to use multiple worksheets in a pivot table. Yes, you read that correctly. By using Power Pivot, you can use the data from multiple worksheets without having to bring the data in via a LOOKUP function.

Accessing Power Pivot

If you are using Excel 2016, it is easy to activate Power Pivot, through the Options menu, on the File tab.

From there, choose Add-Ins from the Options menu:

From here, drop down to the bottom of the dialogue box and choose COM Add-Ins. (Don’t ask me why this isn’t under Excel Add-Ins—it is beyond my pay grade!)

Don’t forget to hit Go…!

Then you will see this dialogue box:

Select Microsoft Power Pivot for Excel. (You can also select any other options you want here.)

Now, when you return to Excel, you should see the Power Pivot option on your ribbon:

(You may have to restart Excel.)

If you have Excel 2010 or Excel 2013, you will want to follow these instructions, from Microsoft:

https://www.microsoft.com/en-us/download/details.aspx?id=102

Now that we have all of that out of the way let’s go!

Let’s select Manage and see our options because that’s where it gets interesting.

Most of the Excel 2016 UI looks the same when you are in Power Pivot. The big change is in the ribbon.

The first thing we should notice is that we can get data from other sources right here (see the options above). This can be confusing because we launched from Excel. Why can’t we see the data that may be present in that same Excel file? Think of a Power Pivot portal to a different universe. Yes, you accessed it from Excel, but it is its own application (just as Power Query Editor is).

If I choose From Other Sources, for example, I will see this dialogue box:

(Note that I scrolled down in this dialogue box so you can see the Excel option at the bottom. There are lots more options here than I am showing.)

I am opting to select an Excel file because that is what most of us are comfortable with. Once I select Excel File, and then Next, I will see this dialogue box:

Don’t forget to check this box, telling Power Pivot to use the first row as column headers. This is weird because you would think that if the columns already have headers, they would transfer over automatically. Apparently not…

And of course, you have to tell Power Pivot where the Excel file is. Then click Next.

I am going to select the Northwind file, because it is readily available to anyone, and is easy to understand.

This may look different than anything you have seen before in Excel. Notice that it uses the terms Tables and Views. These are database terms. For our purposes, a table is the equivalent of a worksheet. (A view is specific database functionality, so we won’t worry about it here.)

Check the boxes for the worksheets you want to extract. (Remember our exploration of terms?) Once you have checked the boxes next to the worksheets you want, click on Preview & Filter.

You can also filter rows you don’t want to bring in. And of course, you can click Clear Row Filters if you need to. Once you are done selecting rows and columns, hit OK, and then hit OK, and Finish.

You can choose the columns you want to bring into Power Pivot. You don’t have to bring in an entire worksheet.

You will then see this screen (after it finishes importing the data) you selected:

You will then hit Close, and it will bring the data into Power Pivot. And here’s where it starts to look different from Excel.

This is the Data View.

We are in the Data View right now.

What is this split-screen?

Above the line is all of your data. Below the line is the Calculation Area where you can enter measures, which are formulas and functions in a language called DAX. DAX is beyond the scope of this e-book, but certainly worth your time and energy. If you want to make the Calculation Area disappear, you can do so in the Views area in the Ribbon.

Your data is still organized into sheets down here.

The other main view is the Diagram View.

This is a diagrammatic view of the data model. Each one of these boxes is a table in the data model. If we think about Excel, these correspond to worksheets in Excel. If you brought the data in from a database, these would be the tables (or views) that you chose from the database.

Let’s talk about the data model again. (If you need a quick refresher on what exactly a data model is, refer back to our Terminology section.)

The data model is a container in which you put all the disparate data that you are importing. In the Diagram view screenshot above, you can see that each box represents a table. Right now, the tables don’t have any connection to each other. So that means that you wouldn’t be able to build a pivot table using these different tables, as the data model stands right now. And remember, that is the whole point of using Power Pivot.

So let’s establish some relationships, shall we?

The common-sense approach to relationships is to look for fields that have the same name, usually an indicator that the contents are the same. But although this rule of thumb usually works, it is not infallible, so verify that the contents of two fields with the same name are, in fact, the same. In our case, we can proceed because I know that the fields that I am connecting with a relationship are the same. You can create relationships by selecting a field in one table and then dragging your mouse over to the field in the other table.

In the screenshot above, you can see that I have created relationships between multiple tables.

You can see that the Order ID fields in both the Orders Details and the Orders table have been connected. It’s kind of hard to see, there is a one to many relationships between these two tables in this field. In plain English, it means that in the Orders table, there is one record for each Order ID. In the Orders Details table, there are many records for each Order ID. If you think about this logically, it makes sense. The Orders table contains a record for each Order ID. The Orders Details table contains multiple records for each Order ID—i.e. the details for each Order ID (individual items on multi-item orders). The many sides of the relationship is indicated by the asterisk (*) and the one side is indicated by the number one

Look closely at the rest of the relationships.

The Orders Details table is connected to the Product table on the Order ID field. The Orders table is connected to the Employees table on the Employee ID field. Think about it, an order is associated with an employee who made the sale, and details about the employees are listed in the Employee table (things like their address, phone number and emergency contact details.)

The Categories table is connected to the Product table by the Category ID. The Product table lists multiple products, each of which could belong to the same category. The Category table lists all the categories, with only one row for each category.

The Orders table is connected to the Customers table on Customer ID. The Orders table lists many orders, and a customer is associated with (hopefully) multiple orders. The Customer table holds the details on each customer, such as an address, key contact, phone number, credit terms, etc…

Now let’s address the one unconnected table, the Shippers table. It isn’t connected to anything right now, which means that you can’t use any of its’ fields in a pivot table with other tables. We need to see what the actual data is in this table and compare it to data in another table in order to figure out how we can connect it.

The easiest way to do this is to switch back to the Data View, and then select the Shipper tab at the bottom (just like in Excel).

Now we need to look at the Orders table, to see if there is a field there that we can use to connect the Shippers table. (Full disclosure, I am cheating a little because I do know this data. In real life, you may have to try multiple tables to find the right one.)

Here you can see that there are three fields in this table: ShipperID, CompanyName, and PhoneNumber.

And what a surprise (not!)—it turns out that there is a field that links the two tables. It is the Shipper ID. In the Orders table, this field is called ShipVia. In the Shippers table, it is called ShipperID. This is a scenario that you will likely encounter, given that you may be pulling together data from different sources. The good news is that you can connect a field in two different tables, even if they are not named the same thing.

Now we have the Shippers table connected to the other tables in the data model.

Relationships have directionality. If you look closely at the screenshot above, you will see that there is an arrow pointing in one direction. Most relationships have a single direction. It is possible to have a relationship that is bi-directional, but in general, bi-directionality should be used sparingly.

Directionality indicates how you can make use of the data in each table.

For example, I can create a pivot table that is based on OrderID, and I can use the ProductID field from the Product table because that relationship goes from the Product table to the OrdersDetails table. The way I like to think of it is that I can pull back the CategoryID from the Product table and use it in a pivot table based on ProductID OrdersDetails table because the direction allows for the data to travel in that direction.

Don’t think that you can solve problems in a data model by simply implementing bi-directionality in a relationship. It adds more complexity to the data model, potentially more time to any processing, and won’t necessarily solve the problem. Again, bi-directional relationships should be used sparingly, and only once you are a data modelling ninja (in other words, you know what you are doing!)

Creating a Pivot Table

Right from inside Power Pivot, you can initiate a pivot table.

I am still inside Power Pivot.

If you click on the Pivot Table tile, it immediately creates a pivot table: The pivot table comes in on a new worksheet (if that is what you want) just as in regular Excel.

What is different here is not the pivot table interface. Look closely and you can see that instead of fields there are tables.

Here you can see a better view:

Here you can see the Shippers table expanded.

Here you can see the Orders Details table expanded. Each of the fields represents an individual column in that table (just as in a regular pivot table)

Let’s take a minute to examine this pivot table in detail.

I am counting the number of orders placed by each customer by category name. If you take a look at the Orders Details table (from which we are pulling the OrderID field that we are counting), you will not see anything about categories or customers.

No mention of categories or customers in this table.

This is where the power (maybe I should call it magic) of relationships shines. Remember that the Orders Details table is connected to the Product table by a one-to-many relationship on the Product ID field. Then the Product table is connected to the Categories table by a many-to-one relationship on the Category ID field.

So when we use the OrderID field from the Orders Details table and then pull in the CategoryName field, Power Pivot does something like this:

“Oh, you want the CategoryName? Let me see…

I don’t see it here in the Orders Details table.

Oh ok, I can get that by going to my Product table, because the Orders Details table is connected to it.

OK, I am over in the Product table, looking at the record with the ProductID that matches the OrderID.

You want the Category Name? Hmmmm…

All I can give you is the CategoryID because that’s all I can see in this table.

But wait….! I can go to the Categories table because the Product table is connected to the Categories table.

….All right, I am in the Categories table looking at the record with the CategoryID that I got when I was in the Product table.

What was it you wanted? Oh right, the CategoryName. Got it! Here it is…

Now let me check if I can bring it all back to the pivot table.

Are all my relationships in the right direction? Yup! Here you go….”

OK, that is using artistic license, but the logic is accurately represented. Talk yourself through the process so that you can see how the relationships connect fields in different tables, enabling you to use different fields in the same pivot table.

Let’s do it again, looking at the CustomerName. Just like Categories, there is no mention of Customers in the Orders Details table.

No mention of categories or customers in this table.

Power Pivot does something like this:

“Oh, you want the CustomerName? Let me see…

I don’t see it here in the Orders Details table.

Oh ok, I can get that by going to my Orders table, because the Orders Details table is connected to it on the OrderID field.

OK, I am over in the Orders table, looking at the record with the OrderID that matches the OrderID in the Orders Details table.

You want the CustomerName? Hmmmm…

All I can give you is the CustomerID because that’s all I can see in this table.

But wait….! I can go to the Customers table because the Orders table is connected to the Customers table.

….All right, I am in the Customers table looking at the record with the CustomerID that I got when I was in the Orders table.

What was it you wanted? Oh right, the CustomerName. Got it! Here it is…

Now let me check if I can bring it all back to the pivot table.

Are all my relationships in the right direction? Yup! Here you go….”

You get the idea! I recommend that if you are still confused, go back to Excel, and follow the logic manually. Look at the Orders Details worksheet, then find the Customer Name and the Category Name in the other sheets. Make notes on the logic that you follow to get that data. Your brain intuitively creates the relationships that have to be created in the data model. You don’t even notice it unless you stop to think about it.

The ideal scenario is that you create all the relationships you need before starting your first pivot table in Power Pivot. However, you may run into a situation where Power Pivot prompts you with this message:

If this happens don’t panic! Here’s what it looks like if you click on Auto Detect:

In this scenario, there were no relationships to be created. (Sometimes Power Pivot gets it wrong!) But if there were relationships needed, or you wanted to create a new one, select Create (instead of Auto Detect) and you will get this menu:

You will select the table you want to start with, our case Order Details. That would go in the Table box (highlighted in blue). Then you would select the field you want to use to create a relationship. In this case OrderID. Next, select the table you want to connect to. In this example, we will use Orders table and OrderID again. So if you forget a relationship, you can always go back and connect two tables.

What about the Data Model?

You may have noticed something called the Data Model. I mentioned it earlier in the Terminology section, but we haven’t talked about it directly. When you get data (here I am deliberately using the term that the ribbon command has) to use in a pivot table with multiple worksheets you have the option to either Load or Load To. from either the Power Query Editor or directly from the Navigator.

Loading from the Navigator

We can choose Get Data, just like we did in Error! Reference source not found.. Once we have chosen a data source, and selected the tables we want from it, instead of clicking Load (like we did last time) we can choose Load To…

There are a couple of things to notice in the screenshot above:

  • You can choose to create a table, a pivot table, a pivot chart (which automatically creates a pivot table), or just a connection. If you keep it at the default, which is Only Create Connection, the data is ready to be used, but it isn’t actually placed into your workbook.
  • The “Add this data to the Data Model” is checked by default. You could uncheck it, but, after all, that is really the point of the Load To… option so why would you?

Let’s assume that you are going to leave these options as selected. This is what you will see after it brings the data into the data model:

The Queries list shows all the tables you have imported. In the Connections, you will see the data model that you have just created. If you now choose Insert>Pivot Table from the regular Excel menu, you will see this:

The Use This workbook’s Data Model option is checked by default.

All the other options are the same as when you create a regular pivot table. You click OK and proceed as normal.

Figure 60. A new panel on the Power Pivot screen

The difference between this view and the one you see in a ‘normal’ Excel pivot table is that you can see the Queries & Connections pane, in addition to the PivotTable Fields pane. If you don’t want to look at the Queries & Connections pane, you can close it by clicking on the X.

Adding data to the Data Model

That’s all well and good, but what happens when you want to add data to the Data Model? Do you have to add from scratch? Of course not!

We can add data as/when we need to.

First, we go into either the Data tab on the ribbon and find Manage Data Model:

Or on the Power Pivot tab, choose the Power Pivot tab, and then choose Add to Data Model.

(Quick side note: you have to activate Power Pivot to see these menu options. If you missed those instructions, go to Accessing Power Pivot for a refresher.)

In this case, I created a worksheet for dates, because I want to add a date table to my data. (A date table is a wonderful thing, and worth learning how to do. Unfortunately, it is out of the scope of this ebook, but I encourage you to check it out soonest.)

Select the data you want to add to the Data Model, in this example, I’m going to add some dates as shown on the next page.

Once you have identified the data that you want to add to the Data Model, you click Add to Data Model. Then it will rattle and hum for a minute (or two), and you will then see the Data View screen with the Date table added.

The new table has been added, but initially is not connected to any of the other tables.

You now need to connect it to the other tables. The best option is to connect it to the Orders table, on the Order Date field.

Here you can see that the Dates table is connected to the Orders table on the Date table (in the green boxes).

Figure 61. The Dates table is now in the Data Model

Now you can use these dates in a pivot table. This is true for any data that you want to add to the data model.

Conclusion

So we have come to the end of this introduction into using Power Pivot and the Power Query Editor with Excel. I wanted to create this e-book because Power Pivot and the PQE are often overlooked, or indeed unknown, by Excel users, even power users (no pun intended). Both tools can really save you time and improve your analyses of complex data.

There is lots more to learn with both of these tools, and in this guide I wanted to get you started. From here you can branch out into more sophisticated use of these tools. There are lots of resources available to you on YouTube, on Microsoft Press and Apress books, and lots of blogs.

Please don’t hesitate to reach out to me at Jessica.Jolly@AltEnterTraining.com. I will be happy to help you on this journey.

Have fun!

0000-00-00 00:00:00


Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}