Locked lesson.
About this lesson
Sometimes you want to build a solution that pulls its data from a different Excel workbook. In this lesson we will show you how.
Exercise files
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.
Getting data from external Excel files.xlsx19.5 KB Getting data from external Excel files - Completed.xlsx
25.7 KB
Quick reference
Getting Data from External Excel files
An overview of building a query using a different Excel workbook.
When to use
When the data to be used in Power Query is saved in a separate Excel file.
Instructions
Getting started
- Create a new query --> From File --> From Excel Workbook --> browse for the file to import
- Select the objects you wish to connect to and click Edit
Which Excel objects you can/cannot access
- You can connect to Tables, Named Ranges or Worksheets
- You cannot connect to Dynamic Named Ranges
- You can also connect to the root of the Excel file (by selecting the folder) to see all objects
Connecting to worksheets
- Importing data that isn’t already in a table might require additional cleaning and transforming
- As many Excel solutions are built ad-hoc and unstructured, it can be difficult to predict what users may do to the worksheets in future
- To keep only certain columns, it is a good practice to select the ones you need --> right click the column header --> Remove Other Columns
Hints & tips
- To import multiple data sources from the file into the query, check the Select Multiple Items box in the preview window
- Date data types do not need to be set by locale as they are already in Excel’s serial number format
- 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.