Getting Data from Excel Ranges

Quick reference

• 00:05 In this video,
• 00:06 we're gonna look at trying to pull data into Power Query from a range.
• 00:10 But the challenge, of course, is that as soon as we try and
• 00:13 actually create a new query against the table or
• 00:16 range here, Power Query's gonna go and try throw a table style on it.
• 00:20 But what if you don't want a table style?
• 00:22 Maybe you don't want the formatting, or more importantly,
• 00:24 maybe you don't wanna lock down the headers.
• 00:26 Because a table must lock down the headers, they can't be formulas, and
• 00:31 oftentimes, we wanna drive our headers with formulas.
• 00:35 So in that case, we can't use a table.
• 00:37 The best thing to do at that point is to go and create a range.
• 00:41 Now I'm gonna go and select my data here, and
• 00:44 what I'm gonna do is I'm gonna give this named, or this area a name of its own.
• 00:49 So instead of calling this A5, which is the name of the active cell,
• 00:53 I'm gonna go and click right here, and I'm gonna click this one rngData.
• 00:57 So I like to preface my names with R-N-G.
• 00:59 That way, it doesn't conflict with the table that Power Query will create at
• 01:03 the end, and it makes it really easy to find them in the name list.
• 01:07 When I'm talking about the name list, what I'm talking about is this.
• 01:10 If I click outside here, I can go to any worksheet whatsoever.
• 01:14 And when I click on the name drop-down here,
• 01:16 you'll notice that I can grab rngData, and it will select it.
• 01:20 And it will actually update the name here to represent what I've selected, and
• 01:25 this is super important for Power Query.
• 01:27 I always recommend that you select a cell elsewhere,
• 01:30 select the named range to make sure that it goes to the right place.
• 01:35 And then you go and create your new query, once you see your named range here,
• 01:39 to pull in from table arrange.
• 01:41 Once you have that named range selected, it will actually pull it in.
• 01:45 And you'll notice that it brings in the original source,
• 01:48 which is pointed to rngData, and then promotes headers.
• 01:52 Because, of course, it's not a real table,
• 01:54 it doesn't have an actual table style behind it that contains the headers.
• 01:57 And changes the types, which naturally,
• 02:00 we're going to force the date to be just a date.
• 02:05 And that's pretty much what it takes to actually create data from a range.
• 02:09 But the challenge with name ranges, and let me just go and
• 02:13 call this table something else, I'm gonna call this one FromRange.
• 02:18 The problem with these guys is that it'll load out nicely,
• 02:22 but if you go and you add more data to your named range?
• 02:27 Let's say, for example, we go with 2017-1224,
• 02:32 and we'll go and put another Lovable Kitten by John,
• 02:37 and it's 12, and 45, and 1.35.
• 02:41 If I now go and update this data, right-click and refresh,
• 02:45 the challenge that we have here is that it doesn't bring in our new Lovable Kitten.
• 02:52 And that's why tables are better than named ranges,
• 02:55 because they automatically expand.
• 02:57 In order to deal with this, I actually need to expand the name range manually.
• 03:02 Which I would by going to the Formulas tab,
• 03:05 going to Name Manager, grabbing the rngData range.
• 03:09 And saying, this one now needs to go up to row 36.
• 03:12 Hit the little check mark and say OK, before this will actually update.
• 03:20 Of course, tables automatically expand, so we don't have to do that,
• 03:23 which is what makes them better.
• 03:24 Now, there is another technique that works in order to deal with these automatically
• 03:29 expanding ranges, when you're actually dealing with a range, not a table.
• 03:33 It's a technique called a dynamic named range.
• 03:35 I'm not going to explain this in detail,
• 03:37 because because this is a super-advanced Excel technique.
• 03:39 But if you do use dynamic name ranges, I do want to show you how you can actually
• 03:43 build one of these guys in, and get it to actually work correctly.
• 03:48 The way that we would start is, I'm gonna copy all the formula here,
• 03:51 except for the very first hyphen that's converting it to text.
• 03:54 I'm gonna go to Name Manager, and I'm gonna create a new range,
• 03:59 this range is gonna be called rngDynamic.
• 04:03 The formula that I'm gonna use is the entire formula that I copied,
• 04:06 including equals.
• 04:07 And the challenge you'll see here is it doesn't actually resolve any data for
• 04:12 me, and that's because this formula is evaluated at runtime.
• 04:16 The real reason that that's a big problem for
• 04:18 us is because you cannot select it from this drop-down list.
• 04:22 Instead, if you want to refer to a dynamic name inside Power Query,
• 04:27 what you need to do is create your new query from other sources,
• 04:34 from Blank Query.
• 04:37 This will take you into a blank query area.
• 04:39 Now, if you don't have the Formula bar showing, you really need this right now.
• 04:43 So we have to go the View tab and click the Formula bar.
• 04:47 Once we've done that, then what we can do
• 04:51 is we can type in this little formula here.
• 04:56 Equals Excel, with a capital E, dot Current,
• 04:58 with a capital C, Workbook, with a capital W, open-closed parenthesis, Enter.
• 05:02 This formula must be spelled and cased exactly correctly.
• 05:08 Once you do, you'll notice that you can see FromRange, the table that was created
• 05:11 from our last query, rngData, our original range name, and rngDynamic.
• 05:18 Now, just as a caveat, if you're pulling this from Power BI Desktop, or
• 05:21 if you're in Excel 2016, non-subscription version.
• 05:24 You may not see the dynamic named range,
• 05:26 because that version of Excel shipped with a bug, but everybody else will see it.
• 05:31 So if you're on that version of Excel, highly recommend you get on subscription.
• 05:35 We're now gonna click this green word here that says Table, by rngDynamic, and
• 05:40 it will drill into the table.
• 05:41 And you'll notice that Power Query drills in, just like it was a source, promotes
• 05:46 headers, changes the types for us, at which point we can override the data type.
• 05:53 So everything else there is pretty much the same,
• 05:55 it's all about starting from that blank query.
• 05:57 I'm gonna call this guy here, FromDynamic, and now I'm gonna load it out.
• 06:03 Close and load, and it'll create me a new worksheet, called Sheet2.
• 06:08 I'm now gonna go back to the dynamic named range, and
• 06:11 we're gonna do the same thing that we did before.
• 06:16 So here's our date, here's our Lovable Kitten from John, and
• 06:20 it's gonna be 12, and 45, and $1.35.
• 06:23 And now, you'll see the difference between using a dynamic named range and
• 06:28 a regular named range.
• 06:30 Right-click, refresh, and this guy will automatically pull in.
• 06:34 So if you do use dynamic name ranges, they do work in Power Query.
• 06:38 Provided you're not using Excel 2016, non-subscription version, or
• 06:42 Power BI Desktop,
• 06:43 because it doesn't have the Excel engine to dynamically refer to things on the fly.
• 06:48 But any other version of Excel will work beautifully.

Lesson notes are only available for subscribers.