2.2 Load an Excel spreadsheet - Video Tutorials & Practice Problems

<v Instructor>In this particular sub lesson,</v> we are going to look at loading data from an Excel spreadsheet. And this is a very common source of data in many organizations. So let's begin. Okay. So I have my spreadsheet saved from my previous sub lesson. And I can see that I'm on the data tab here, and I wanna go to my queries and connections. I wanna see what queries and connections I currently have inside of this particular spreadsheet. So I'm gonna click on this right here, and we're gonna see that I have the sales territory. And if you completed the first sub lesson, you will know that we were able to bring this one into the mix. So let's go ahead and bring another set of data into this particular sheet. So what I'm gonna do is I'm gonna go over to get data, I'm gonna click the drop down box here, and we're gonna see is from file, and I'm going to choose from a workbook. Like so. We will see that the data import dialog box is open here. So I can then go ahead and choose sales. So I'm gonna go ahead and choose this as my source. Cause right now I'm only looking at cell sources and I'm gonna click on import. It would let that dialog box come up. So what we'll see here is we have a navigator window that shows up that is giving us a preview into that Excel spreadsheet and the different objects that we can connect into using the power query editor. So what I'm gonna do here first is I'm gonna to click on this first object that says tblSales, and we will get a preview what that data looks like inside of that structure. And then I'm gonna click on this other object called shtSales and get a preview of the data. One difference that you will likely notice is when I clicked on shtSales, is I get some extra data up above my data table. What appears to be a data table. Whereas when I click on tblSales, I'm only seeing the data in the actual table itself. So let's explore why this is the case. What I'm gonna do is I'm gonna actually open up the source Excel spreadsheet and to show you what the navigator window here is seeing. So what this tblSales is and what shtSales is to explain it. So let's go ahead and open the source spreadsheet up. So as you can see, I have the source Excel spreadsheet open up now that I was just previously connecting into. And we can see that at the very bottom of the screen here, we have this thing here called shtSales. So this is the sheet name, and that is bringing all the data in off of the sheet. So we can see we've got a title and it looks like a subtitle here, plus all the data in here. If I go ahead and put my mouse inside of this structure right here and go up to the tabs, I can see that I'm actually inside an Excel table. And if I click on this table design, I can see there's a table there called tblSales, which has all the data inside of this Excel table. So what is happening now is when we connect into this file, we can either connect into the sheet itself and all of its contents, or we can connect into the Excel table and it's contents only. So what I'll do here is I just wanna highlight that if I connect them to shtSales. So going over to the next one, I'm gonna see that I will see this data here plus this blank row and then the data in the table. But when I connect into tblSales, all I'm gonna see is the Excel table itself. So a preference of mine is when I'm pulling data out of Excel spreadsheets that I like to see the data structured in an Excel table, if it is all possible. It makes the import process a whole lot easier cause we're only gonna be pulling the data that is really the table versus having to clean up all the things in and around the table. But you may not have that choice. You may need to connect to the sheet and do that cleaning on your own inside of power query. It's not that bad, but let's say I just prefer if I can connect into an Excel table. So let's go ahead and get back over into the spreadsheet that we're using to import data into and walk you through this process again. Okay. So we're back in the spreadsheet now where we are bringing our data into, and we can see now that I am connecting onto the object called tblSales, and I'm only looking at the data inside of the Excel table. And once again, if I click on this shtSales, I'm actually seeing the data that is inside of the table plus the header information that we saw up above the table. So I'm gonna choose tblSales cause that's what I wanna connect into. And now let's go down to our load. And once again, I'm gonna say load to, wait for my import dialog box to show up. I'm gonna say only create connection and then gonna click okay. And here we can see once again in the queries and connections pane, I can do a preview that data within tblSales plus take a look at all the other metadata. So what are the columns? I can see there's 14 columns here, was last refreshed at 12:08, this data has been loaded nowhere, and here is the actual data source. So keeping in mind that I do wanna make sure I have good naming conventions, let's go and rename this query. So I'm gonna click on these three dots once again, go down to properties and here it is, I'm gonna say sales, click okay. And now I have my query here with a good name. That is the end of sub lesson number two.