Using External Data

Quick reference

• 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.