Locked lesson.
About this lesson
Sometimes trying to extract data from a column can be difficult, so why not let Power Query help? Microsoft has put significant effort into a tool called "Columns From Examples" where you can define and refine the output, and let Power Query build the formula to get the results.
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.
Create Columns From Examples.xlsx19.3 KB Create Columns From Examples - Completed.xlsx
27.2 KB
Quick reference
Create Columns from Examples
An overview of Power Query’s ability to generate a new column based on sample data
When to use
When you have a column of data and want to extract from or modify it, but don’t know how to get started
Instructions
Getting started
- Create a new query to bring your data to Power Query
- Go to the Add Column tab --> Column From Examples
- A new blank column will appear on the right side of the query table
- In the first cell, start typing in a sample of the data you would like to extract, and press Enter
Working with and refining example results
- If the column looks like it contains the data you want:
- Double click the header to rename it and then click OK
- The new column will be added to the query table
- If the data generated in the sample column is incorrect on some rows:
- Refine the results by typing over one of the incorrect results
- You may need to refine the result several times
- If the sample column fills all remaining rows with null values:
- This means the pattern is too complex and no refinements will help
- Try breaking large, complex patterns down into smaller columns and then building the results from those columns
Hints & tips
- This feature is NOT available in non-subscription versions of Excel 2016, but is available in:
- Excel 2010 and 2013
- Subscription versions of Excel 2016
- Power BI Desktop
- The formula Power Query generates for populating the new column is displayed at the top of the Query Editor
- You can control which columns are being used to generate the data by (un)checking the boxes at the top of each column in the Column From Examples interface
- Unlike Excel, you can remove the columns used to generate the new column without causing #REF! errors
- 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.