digest
Welcome to Power Pivot video number 12. Hey, if you want to download this workbook as well as a couple other workbooks to follow along, click on the link below the video. Hey, now what we're going to do in this video is we're going to look at Power Query to import and transform data, dump it either into Excel or we're going to dump it into our data model and then create a report based on our data model.
Let's go look at our data. We're going to start off. We're going to have transactional data. Notice the product ID that we get from this system has some extra characters at the end. Our product data will allow us to group by product name and by company name, or the larger category name. The product ID is missing those characters on the end. So as we import the data here, we will transform this column. Not only that, but we're actually going to connect, because we would like to have a column for product name and company right here in this table. This isn't a gigantic data set. We don't need to worry about normalizing. Not only that, but when we import it into the data model, each of the columns will be stored in that columnar database, which does a good job of compressing. So again, we're going to clean up this column and add two extra columns based on a connection between this product and the product over here.
All right, we're going to close these files. Now, the first thing we're going to do is go to Power Query, and we need to import get external data from file, and I'm going to go from Excel. There's our transactional source data. Double click. The navigator window will open. This is a sheet. This is our table. I'm going to right-click and edit. Edit the query. Now, here's our table just as we saw a moment ago. This invoice number we're never going to use for any of our reports, so I'm definitely going to right-click and remove it. We're already transforming the data. We're already getting a list of steps over here.
The next thing is this product ID. The entire ID up to the second dash is correct. Everything after does not match our source table. So we're going to select the column, come over to split column by delimiter. We want not a comma, but we want custom, and we're going to type a dash. And check this out: at leftmost, at rightmost delimiter. When I click okay, it will split. Now this is the column we need to connect with the other table. This one we don't. So I'm going to right-click. You could already see the list of steps for this query expanding. Right-click, remove. And there's yet another step. Now this column ID-.1 is not what we want. So I'm going to double-click and get rid of the one. Enter. That is going to work for us. We still have two more columns, but we have to load this. Close and load two. And we want only a connection at this point. And load. There is our workbook queries. I'm going to right-click edit and change that one. Backspace. Tab. Close. Keep. Keep.
Now we have to go get our second table. Power Query from file, and we want from text. There's our file. Double click. Now we can already see a problem here. It didn't interpret the field names correctly. The data type was all text, and so were the field names, so it didn't interpret it. I'm going to come right to the upper left corner and click that drop-down and say use first row as headers. Boom. There we go. Now we've got one, two, three columns. That's all looking good. We want to come over here and name this D product and enter. Now we can close and load two only, create a connection, and load. Now we have our two tables.
Now check this out. This is going to be quite amazing. We can go up to Power Query, and really, remember what we need to do is load from the product table into the transactions table, as if we were doing a VLOOKUP for the product names and categories. Now, when I first started trying to do Power Query merge, I was thinking that's what we did in our last video, append, but really merge is going to allow us to create a relationship between two tables and populate this transaction table as if it were a VLOOKUP looking at products. So, check this out. We're going to do merge. Our first table will be transactions. I'm going to click the product ID right off the bat. Now, I'm going to go to the product table and select the product ID. By selecting these two columns, we're in essence creating a relationship, or some sort of VLOOKUP. I'm going to click okay. And now we can edit this. Look at this. This whole column says table. There's a relationship between these tables. And watch this. I'm going to select all and say product and company. And boom. This will create two columns, as if there were a VLOOKUP to that other table.
Now, these are probably not good names. I'm going to call this product, call this one company. And guess what? I do not need this column here, even though I originally imported it, transformed it, and built the relationship on it. Now that I've imported these two things through this query, I'm going to right-click and remove it. These are the only five columns that I'm going to need for my data model and pivot table. Close. Actually, let's name this. We've got to give it a good name: final sales and cost table. Enter. Close and load two. And here's where we have the choice. We could dump this right in here. Even if you're never going to use Power Pivot, that's pretty amazing. We imported this, transformed the data, did all this relationship VLOOKUP stuff, and then I could just dump it there as a brand new table that is refreshable. I'm not going to do that. I'm only going to create a connection and add this to the data model. Click load. And there it is: 1,421 rows loaded.
We could go up to Power Pivot, Manage, or Alt-M. Now we have a single table. I'm going to come over here, change the format to date, highlight, and let's see, double-click, make it big enough. Highlight both of the first two cells, and let's see, do English. I'm going to come down here and create a single measure, net profit, colon, equal sign, x. I'm in essence going to iterate over this table, and I want to do it in a calculated field, not a calculated column. I'm going to iterate over the table for the final sales and cost table, and the expression is final net sales minus the cost, close parenthesis, and enter. Come up here and let's add some currency. Now, we could go wild here, but the main point of this is that we did this amazing import into Power Pivot. We are going to make a pivot table on a new worksheet. Click okay. All in one table. Look at that. I'm going to drag net profit down to rows. I'm going to drag product down to rows. And I'm going to close that right there. Then I'm going to click inside the pivot table, and I want to insert a slicer for company. Click okay. And there we go: Gel Boomerangs, Colorado Boomerangs, Trifly.
Now, we absolutely could have done this over in Excel. We did not have to use Power Pivot, but I wanted to show how amazing it is that we can transform our data and drop it either into a table or into Power Pivot. I better name this. All right, so we definitely saw how to dump some data into Power Pivot, all starting with our amazing Power Query. All right, we'll see you next video.