Create Columns from Examples

Quick reference

• 00:04 In this video, we're gonna look at a very cool feature that is one of the very
• 00:10 few that is only available to current builds of Excel or Power BI Desktop.
• 00:16 What that means is that Excel 2010, 2013 and
• 00:18 the subscription version of Excel 2016, you'll all have this.
• 00:24 But the non-subscription version of Excel 2016, sorry, you're out of luck.
• 00:28 This came out way later than the actual release of that particular software
• 00:32 which means you won't see this feature at all and there's no way to replicate it,
• 00:36 which is unfortunate.
• 00:38 So what I wanna show you here, we're gonna go and
• 00:41 we're gonna grab some data from file from text or CS feed.
• 00:46 We're gonna drill down into the GoSkills data files, and
• 00:51 the file that we're looking for is in columns from examples.
• 00:54 And it's called FireEpisodes.
• 00:56 And we're gonna import this.
• 00:58 Now this is a listing of all of the episodes of the Chicago Fire TV show.
• 01:04 And what I'm gonna do is I'm going to go and pick off Fixed Width, and
• 01:09 I'm gonna set this to 0.
• 01:10 And when I hit Tab, you're gonna see that the preview is gonna recalculate,
• 01:13 and it's gonna make this very messy.
• 01:15 It's gonna put it all back into a single column.
• 01:17 There's a reason why I'm doing this right now,
• 01:19 is to really show you how this particular feature actually works.
• 01:23 So let's click Edit.
• 01:26 The feature that I'm gonna demonstrate is called create columns from examples, and
• 01:30 it's found under the Add Column tab.
• 01:32 You'll notice there's a big button right here.
• 01:34 And the purpose behind this is to allow us, when we're looking at something like
• 01:39 this going, holy cow I got no idea where to start.
• 01:42 This will allow me to actually start building patterns and Power Query
• 01:46 will actually help leverage some algorithms behind the scenes to try and
• 01:50 make life easier for me.
• 01:51 So what I'm gonna do is I'm gonna click Column from Examples.
• 01:54 And what I'm gonna try here is I'm gonna try and extract the episode name.
• 01:59 And you'll notice that the episode name here is Pilots, and Mon Amour, and
• 02:02 Professional Courtesy.
• 02:03 So we're gonna give this a shot.
• 02:05 So over in column two, I'm just gonna type in here Pilot and I'm gonna hit Enter.
• 02:11 And notice that right away, it fills all of the names of the individual episodes
• 02:16 all the way down, and that's pretty cool.
• 02:18 So, if I go and scroll down this list you can see that it's done it.
• 02:22 Even better than that, Power Query has actually given me the formula for
• 02:27 what's going on.
• 02:28 This formula uses text between delimiters, feeds in column one and
• 02:31 a whole bunch of quotes and stuff in order to make this work.
• 02:35 So I can now come back and say, that's pretty cool.
• 02:37 This is gonna be called Episode Name, and say OK.
• 02:43 And then we'll say OK.
• 02:46 So now we've got the episode name, so that looks pretty good.
• 02:50 The next thing that I'm gonna try and do is I'm gonna try and
• 02:52 actually push this one a little harder.
• 02:54 So let's go Column from Examples again, and
• 02:56 this time I'm thinking it will be really cool if I could actually get it to say,
• 03:01 what's the episode name comma, the episode number.
• 03:05 So we'll give this a shot.
• 03:06 We'll go and we'll say, all right, let's go Pilot, Episode 1.
• 03:15 And I'm pretty happy it's auto completed until I realize that this has actually
• 03:19 given me Episode 1 all the way down.
• 03:21 Well that's obviously not what I want.
• 03:23 So I'm gonna try and type this again Mon Amour, comma, Episode Two.
• 03:31 And this is bad news.
• 03:32 When we see it go null all the way down,
• 03:34 that means that it cannot figure out what the pattern is.
• 03:37 So I'm going, okay well if that's not gonna work, let's go back.
• 03:41 We'll delete this.
• 03:42 We'll go back, we'll delete this.
• 03:44 Say all right, forget it.
• 03:45 That didn't go.
• 03:46 Let's try this then.
• 03:48 Let's just go with Episode 1.
• 03:51 And that's completed a bunch of stuff, but obviously,
• 03:53 there's a bunch of stuff here that's incorrect.
• 03:55 So we'll try another one.
• 03:56 We'll go, Episode 2.
• 04:00 And notice that, it's actually doing much better,
• 04:04 although this one here on row ten isn't so happy.
• 04:08 Now the reason for that, is because it's picked up text between delimiters,
• 04:11 and it's using commas as its delimiters but
• 04:13 look at the episode name, Merry Christmas comma etc.
• 04:17 So that comma is blowing things up.
• 04:19 No big deal.
• 04:20 This one's wrong, just try and replace it.
• 04:23 We'll go with Episode 10, Enter.
• 04:27 And now you can see that it's actually correct all the way down.
• 04:30 It's changed to a slightly different formula, so that's cool.
• 04:34 And now we can go on, we can say, okay, well, that's cool.
• 04:36 And let's double click the header.
• 04:38 And let's call this one Episode Nbr for number.
• 04:43 And we'll say okay to commit that one.
• 04:46 What's interesting here is that I can now go back and say, Column from Examples.
• 04:52 Notice that it's picking and it's reading from all three columns so
• 04:56 I can now go here and say, let's try this.
• 04:58 Pilot comma Episode 1.
• 05:03 And now, because it's reading from all the columns,
• 05:06 it's picking those two things up from this area.
• 05:08 So that's awesome, because now I can say,
• 05:10 well this is Episode Name and Number and we're good to go.
• 05:18 Now, I'll show you where this works really well is in simple logic like this where
• 05:22 you're trying to break down some patterns and you wanna see what's going on.
• 05:26 Where it can get a little bit more challenging though and
• 05:28 falls down is when you're trying to get into some more complex things.
• 05:31 Right here, the date.
• 05:32 I'd really like to extract that.
• 05:33 You think that shouldn't be that hard.
• 05:35 So I'm gonna go with 2012-10-10.
• 05:39 And hit Enter.
• 05:40 And the next line is two.
• 05:42 Well, that's plainly not going to do it.
• 05:43 So we're gonna back and we'll say, 2012-10-17.
• 05:46 And we'll hit Enter.
• 05:49 And it's not bad until it gets to, that tricky episode 10 with the comma in it.
• 05:56 Well, let's fit this one here.
• 05:59 So we'll go 2012 comma 12 comma 19,
• 06:04 hit Enter and at that point the list gets blown.
• 06:08 And I've never been able to make this one work yet.
• 06:10 So this is kinda the challenge here is that when you're building some of these
• 06:14 things up, sometimes you'll get into a pattern that doesn't work.
• 06:19 The nice thing though, if you can break it down with enough helper columns,
• 06:22 one of the really cool things about Power Query, unlike Excel.
• 06:25 You know these two columns that we use which actually ended up building this guy
• 06:29 here, as a matter of fact, all three of these columns, right click.
• 06:34 Remove, we don't need to keep them around.
• 06:36 It's not gonna cause any referential errors.
• 06:39 And at this point in time, you'll see if I hit Home, Close,
• 06:42 and Load, this is gonna load out nicely to the work sheet, and
• 06:45 everything will still be there because it works in a sequential order.
• 06:48 Pretty darn cool.
• 06:50 A nice way to actually end up building things up more gradually.
• 06:53 And Microsoft is working on tweaking these algorithms
• 06:56 all the time to make them better.
• 06:57 So one day those examples I showed you that won't work, they just will.
• 07:01 How cool is that?

Lesson notes are only available for subscribers.