Locked lesson.
About this lesson
Connecting to external data sources (such as databases), and pulling the data into Excel.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
2016, 2019/365.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
pos.accdb46.6 MB Using External Data - Completed.xlsx
2.7 MB
Quick reference
Using External Data
Pulling external data into Excel and PivotTables.
Where/when to use the technique
When part or all of the data you need to build your reports is stored in an external file or database.
Instructions
Sourcing data with Power Query
- Go to the Data tab > Get Data > choose the file type you want to import
- Browse to your data source, select it and click Import
- Choose the table(s) or query you wish to import and click the button to the left of Cancel
- Perform any transformations needed
- Choose to land it in a table or a PivotTable
- If you landed it to a table first, go to the Insert menu and add a new PivotTable
- Layout your PivotTable as desired
- Refresh your PivotTable from the database
Key points to remember
Expanding columns
- You can bring in related records from other available tables and queries from the database without having to write a VLOOKUP
Landing data in a table first
- You can “see” the data in the worksheet so you can review it
- You can easily write formulas to manipulate, change or add to the data
- Means that your data set must be less than 1,048,575 rows
- This is the maximum number of rows in an Excel worksheet, not including 1 row for the header
Landing data in a PivotTable first
- You can’t “see” the data in the worksheet so you can review it
- You can’t easily write formulas to manipulate, change or add to the data
- Means that your data set can be more than 1,048,575 rows
- The PivotTable summarizes it before it lands in the worksheet
- 00:04 In this video, we're going to look at how to get data from an external data source
- 00:08 like a database.
- 00:09 Now, this game has kind of changed because in 2016,
- 00:13 Power Query became the new way that we actually get data into Excel.
- 00:17 And you'll find Power Query under your Data tab.
- 00:20 It was built in to Excel 2016 and higher.
- 00:23 In Excel 2016,
- 00:24 there was a button right around this area here that was called New Query.
- 00:28 In Excel 2019 and higher including Office 365,
- 00:31 it's now found under the Get Data button.
- 00:33 And this is where we are going to go to get data from external sources.
- 00:36 You can see there is lots of places to grab it from File,
- 00:40 what we want this time is from database from Microsoft Access Database.
- 00:44 It'll pull up a dialog that I can browse and
- 00:47 find the file that we're going to use for this one which is called pos.accdb.
- 00:52 I'll import that and it'll now take me to a preview window that shows me all
- 00:56 of the tables in the database.
- 00:58 And when I click on something like Categories,
- 01:00 it shows me a nice little preview, or items or even the tblChits,
- 01:04 which is what holds all of my transactional data.
- 01:07 So this is the one that I really want.
- 01:09 Now that I have decided that this is the table that I'm after,
- 01:12 I'm going to go click the button to the left of cancel.
- 01:14 Now this could be called Edit, it could be called Transform Data.
- 01:17 The names of the button change depending on what Microsoft is playing
- 01:20 around with here.
- 01:21 because they want to make sure that you know that this is the button that
- 01:23 you should really be clicking.
- 01:25 And what it'll do, is it'll launch you into Power Query,
- 01:28 the most important data studio that we have.
- 01:31 Why is this so important?
- 01:32 It's because it lets us actually change the information that we're playing with,
- 01:36 before we actually start to use it.
- 01:38 For example, this ID column doesn't add any value to me.
- 01:41 So I'm going to select it and I'm going to press the Delete key to make it go away.
- 01:45 The POS Chit Dates stands for Point of Sale Chit Date.
- 01:49 I'm just going to double click on this and rename it to Date.
- 01:52 So even though it comes from the database one way,
- 01:55 I can change it to look the way I need it to.
- 01:57 Right now it's showing dates and times, I'm not really interested in that.
- 02:00 So I'm going to click on the little icon here and change this to be a date.
- 02:04 I could then look through the rest of the information, say the hour, the minute.
- 02:08 I'll hold down my Ctrl key and select the minute column as well.
- 02:11 I don't really need these, so I'll press the Delete key,
- 02:14 because I'm just going to analyze based on years.
- 02:16 The chit number, the location,
- 02:18 the rest of this looks like it might be interesting but what's going on over here?
- 02:22 We can see table categories and table items, they have these value things.
- 02:25 Well, if I click in the white space beside the screen word,
- 02:28 you'll notice that it brings up a nice little preview down the bottom.
- 02:32 And what this is,
- 02:32 this is the related records from the table inside the database.
- 02:36 What does that mean?
- 02:37 It means that I can actually VLOOKUP against another table without even having
- 02:41 to write a VLOOKUP.
- 02:42 Here's how I do it.
- 02:43 I click the little arrow right here.
- 02:45 I say I don't want any of these.
- 02:47 But what I'm interested in is the description and the group.
- 02:51 And I can now click OK.
- 02:52 And just like that, it's performed a VLOOKUP exact match against the other
- 02:57 tables in the database, pretty slick.
- 02:59 I can then go back and say hey, the items don't need those.
- 03:03 The item code and the category code here, don't really need either of those now that
- 03:07 I've got the real names, so I'll get rid of those.
- 03:10 And I'll even go and just rename these to Category and to just plain old Group.
- 03:18 Power Query records all the steps of what we're doing here and this is so
- 03:21 it can replay it when we refresh it later, which is pretty awesome.
- 03:25 Final thing I'm going to do, I'm just going to rename this table to Transactions
- 03:29 because it's a much nicer name.
- 03:31 Now at this point,
- 03:32 I have to make a decision as to how do I want to load this data.
- 03:35 You can see down the bottom here, I have 199 plus rows.
- 03:39 That means there's more there.
- 03:40 I don't know how many rows there is.
- 03:42 Well, actually I do because I know the database, there's about 349,000.
- 03:46 But you only see a preview in this window.
- 03:48 Now the thing is, if there's more than 1,048,575 rows,
- 03:53 I can't load them to a worksheet.
- 03:55 Because a worksheet has 1,048,576 rows and we need to reserve one for the header.
- 04:02 So basically the rule of thumb is, if it's over a million rows,
- 04:05 you can't land it to a worksheet, you need to land it directly into a pivot table.
- 04:09 Now this one's small enough, I could load it to a worksheet but
- 04:11 we're going to load directly to Pivot Table anyway just to see how that works.
- 04:14 To do that, we can go Close and Load.
- 04:17 We'll hit the bottom half of the button to go to Close and Load To.
- 04:21 And when we do that, it'll actually take us to this dialogue that's going to come
- 04:26 up right now which will actually show us where we can put our data.
- 04:30 I can load it to a table.
- 04:31 Now in Excel 2019 and higher, you also have these options,
- 04:34 direct a pivot table report or pivot chart, or
- 04:37 only create connection we'll just create a pointer to it.
- 04:39 We're going to go direct to a pivot table report and say OK.
- 04:43 And at this point, you'll see that the data starts to stream in on the right
- 04:47 hand side and it's going to give us our 348,000 rows.
- 04:50 Now there's a ton to learn about Power Query, so much,
- 04:53 we have an entire course in the GoSkills category dedicated to this.
- 04:56 So you should really check that out if you're working with your data,
- 04:59 getting data from anywhere.
- 05:01 Now that I have it here, nice and easy, I can go and say, hey,
- 05:03 let's put on our group.
- 05:05 Let's put on our category.
- 05:06 And let's go and throw down, say, our units, and our amounts on our values,
- 05:11 just like that.
- 05:12 We've prepared our data, we've brought it in, and
- 05:14 now we can actually start to use it.
- 05:16 So that's the magic of Power Query is it'll let you connect to all kinds of
- 05:20 different sources from external or even internal places.
- 05:23 Clean up the data and get it ready for your pivot tables so
- 05:28 that it just lands it directly into a cell and away you go.
Lesson notes are only available for subscribers.