Getting Data from External Excel Files

Quick reference

• 00:04 In this video, we're gonna look at getting data from an external Excel Workbook.
• 00:09 As a matter of fact, the one that you see front and center here.
• 00:13 Inside this workbook, we just called Excel Data,
• 00:16 you'll notice that we have a Dynamic Named Range covering this area of cells here.
• 00:21 I can prove that by going into Formulas, and saying Name Manager, and
• 00:25 noticing that we have a Dynamic Named Range.
• 00:28 In addition, we also have an Excel Table.
• 00:31 If I click inside, you can see the Table Tools tab comes up, so
• 00:34 it is definitely an Excel table.
• 00:36 And we can also have a named range, which picks up these six cells down here.
• 00:40 Which we can see by selecting range departments.
• 00:43 So we have three different objects here, we've got an Excel table,
• 00:47 a dynamic name range, and a named range.
• 00:50 And what I'm gonna do at this point, is I'm actually gonna close this off,
• 00:54 and we're gonna go and grab our data from the file.
• 00:58 To do that, we're gonna start a new query, from File, from Excel Workbook.
• 01:03 It will then launch us into a Browse dialogue, and
• 01:07 we're gonna browse down into the GoSkill sample data,
• 01:10 into the data from Excel folder and I'll choose the Excel Data file and same part.
• 01:18 Now when we do this you'll notice that we get a really nice preview when data
• 01:21 pops up.
• 01:21 And it shows us all of the different options that we actually connect to.
• 01:26 Notice that we have the DataTable.
• 01:28 So this is the actual table inside Excel.
• 01:30 It looks beautifully formated, everything looks nice and cool.
• 01:34 We've got range departments and
• 01:36 range department actually gives us these six cells that we actually had.
• 01:40 And yet interestingly enough,
• 01:42 it even appears to have promoted that first load of headers.
• 01:45 And then we have Master.
• 01:47 And Master is the actual entire worksheet.
• 01:52 Notice what's not here, the Dynamic Named Range, that does not show up in this list.
• 01:58 And the reason for that is because the Dynamic Named Range is initiated and
• 02:03 evaluated at runtime as to what it's going to be.
• 02:06 And because the workbook is closed, Excel doesn't have the smarts to be able
• 02:09 to actually go reach into the other workbook and say,
• 02:12 can you evaluate this for me now in order to bring it back.
• 02:16 So you can't pick up a dynamic range in an external workbook.
• 02:21 But you can pick up a worksheet.
• 02:23 And this is actually different than being able to connect to
• 02:25 things within the same workbook.
• 02:27 In the same workbook, you can't pick up an entire worksheet, so,
• 02:32 in this case we're gonna try that.
• 02:34 Should also point out before we leave,
• 02:36 you can bring in multiple queries at the same time, I'm gonna skip that for now.
• 02:41 I'm just gonna select Master and we're gonna say, Edit.
• 02:46 This will launch me into power query.
• 02:49 It's gonna do a little bit of heuristics on it, and then it's gonna come back and
• 02:52 say, all right, this is what I got for you.
• 02:55 Now the danger of working with a worksheet that's not structured in
• 02:59 a table is the data may not be in a perfect format.
• 03:03 It could be ad hoc.
• 03:04 Users might be changing on a regular basis.
• 03:07 So if you're gonna do this you need to be very careful about the structure
• 03:11 of your workbook and you need to anticipate what users might do.
• 03:14 I'm gonna keep this one relatively simple.
• 03:16 What we're gonna do is we're gonna assume that we're going after the data here but
• 03:19 it didn't actually have a table on it.
• 03:21 And so we're gonna pretend.
• 03:23 So for right now, I'm gonna go, I'm gonna select these four columns
• 03:27 by holding down my Shift key and clicking on column 4.
• 03:30 Right click and I'm gonna choose to Remove Other Columns.
• 03:34 This means that if this table expands, if somebody adds another column to it,
• 03:38 it's not gonna come through.
• 03:39 But it means that if people add other columns to the worksheet,
• 03:42 they're gonna disappear as well.
• 03:45 Then, I need to try and figure out the fastest way to pair this data down, so
• 03:49 that I'm only looking at valid items.
• 03:50 And it looks to me like possibly one of the fastest way to do that would be to go
• 03:55 to column 4, and say let's go and filter this to say,
• 03:59 don't give me the null values.
• 04:02 Say OK, that's gonna chop things down quite a bit.
• 04:06 And then when I look at things I can say, hey, you know what?
• 04:08 There's no values in the first column, let's go filter those on second.
• 04:13 We'll get rid of those, and we'll say OK, and
• 04:17 now I'm back down to the original data.
• 04:20 That first row, looks like it's headers, so we'll promote those.
• 04:24 There we are.
• 04:25 And now we can go and set our data types the way that we want.
• 04:28 I'll hold down my control key, select the department column as well, change type.
• 04:33 And I'm gonna force this to text because these are account numbers,
• 04:36 they are not truly mathematic.
• 04:37 And we'll replace current.
• 04:40 Now interestingly enough, the date that's showing up here actually shows as
• 04:45 a proper date when you reach out to an external file.
• 04:47 It doesn't have the time on it.
• 04:49 That's a little bit of a surprise but it's kind of a nice little thing to have there.
• 04:53 I am going to force it again to be a date just because I wanna lock
• 04:56 these data types in.
• 04:57 Notice that there's no need to use locale, whatsoever.
• 05:00 Because again, this guy is reading from date serial numbers that exist in Excel.
• 05:04 So the date conversion's already been done for us.
• 05:07 And finally, we can set our amount to currency.
• 05:10 And at this point, I can replace that one as well and
• 05:14 now I can make my little table, call it transactions.
• 05:20 And I can hit close and load to load it to the worksheet.
• 05:23 And the beautiful thing here is that every time somebody updates the data in
• 05:27 the source file, I can open up this workbook, hit refresh and
• 05:31 it's gonna bring that data in, run it through those steps and land in the table.
• 05:34 So I can have somebody actually maintaining the source data,
• 05:37 while I build their business intelligence in a completely separate file.
• 05:41 Which makes things nice and broken down into a good solution on often cases.

Lesson notes are only available for subscribers.