digest
Welcome to Power Pivot video number 11. If you want to download this workbook and follow along, click on the link below the video. In this video we want to talk about Power Query. This video and the next video also cover Power Query. Power Query is an add-in, and you can get it in 2010 and 2013. 2013 will allow you to load it to the data model. Now, Power Query is simply some power tools to ask questions of data sets. For this example, we're simply going to use it to append. We're going to get sales tables from multiple sources, stack them up on top of each other as a single table, and dump it into Power Pivot. Later we'll see other options where the query part of this means you can do really awesome transformations of data as you import it into either Excel or Power Pivot.
All right, so let's go look at our three files. Right off the bat, we actually have an Excel file. This is the Tacoma sales. We have our Oakland sales in an Access database, and we have our Seattle sales in a text file. So we're going to close all of these, and in our workbook we're going to go to Power Query. Now, we've had tools for importing data for years on the Data tab, but Power Query, although in this example we're just going to take the tables and append them, later we'll see that it really has some powerful transformational tools in it. We're going to start by going to Get External Data. It kind of looks like the Data ribbon, but no, we're going to start off from our database. Access. There's our file. I'm going to double-click. Our Navigator window opens up. I'm going to select the table. I can either click Edit or right-click Edit. This Access data table has one extra column, this unique identifier, that we don't want, so I'm going to right-click and remove it. Now, this is coming in as a date time. I can come over to Transformation and select the data type. Now, again, in this video we're not going to mess around with some of these other cool features. There are one, two, three columns that are the same in all three tables. Now I'm going to go to Close and Load To. I want to only create a connection. Load. Now we have our queries, one query so far, two more to go.
We go up to Power Query and now we're going to go from a file and start with Excel. There's our Excel Tacoma sales. Double-click. We have a sheet and a table. I'm going to right-click Edit and come over here. I think the name I'm going to use is Tacoma sales. The data is looking good. Close and Load To, only create a connection, and load. There are our two queries so far. Power Query. Now we'll go get our Seattle sales from our text file. Double-click. This will open up straight into Edit. One, two, three. That's looking fine. I'm definitely going to name this Seattle sales. Close and Load To, only create a connection, Load. One, two, three.
Now we can start appending. Go up to Power Query, Append. We'll start with Oakland sales. This will be our primary table, which we want to start the append. The second table that we'll add to the bottom, we'll do Tacoma. I'm going to click OK, and I'm going to come over to the name and call this something like All Tables with Sales. Enter. So right now, if we were to scroll down, sometimes it takes a while, but there are the two tables so far. Now we're going to come up to Append Queries and get our third table. This is so cool. Seattle sales. Click OK, and boom, now we have all three tables together. Close and Load To.
At this point you get to decide: do you want to import it into an Excel sheet as a table, or do you want to say only create a connection and add this to the data model? Either way you do it, you are going to get the merged table, all three tables into one. For this example we're going to use the data model. You could click Table, and it would be right in the sheet. We're going to click Load, and there we have one that is loaded, and these three that are just the connections to the three. Boom. We can go up to Power Pivot, Manage, or Alt+B+M, and there you go. There is our merged three tables. We can see the 5,291 records. Now, in this video all we're doing is seeing how to load three tables into the data model or into Excel. In our next video we'll actually see how to transform data from multiple tables and load it into the data model and make some reports. Alt+Tab.
All right, that was a little bit about importing data and merging three tables using Power Query. We'll see you in the next video.