Oz du Soleil explores Add Column by Example, a new feature in Get & Transform in Excel 2016.
- [Narrator] Now let's look at a new feature in getting transformed and it's called add column by example. Here we have a lot of pie data. This is the result of a query where we added some columns. Here is the source data. Let's go back into the query and add columns by example. We're going to play a little bit here. Data, show queries. And we're going to edit.
Okay. Here are the pies that were ordered and we've got our Monday through Wednesday data. And we've done math to get slice equivalence because a pie is worth eight slices. And we calculated the slices and we have total slices. Alright, first of all. Just to clear some room let's get rid of Monday through Wednesday. Highlight. Hold down the shift key. Select Wednesday, right click, remove.
Now we'd like to say apple pie whole equals 232 slices. We could do that via a custom column. But we're going to do column by example. Add column. Columns from examples. Okay. So it's asking me for an example. Apple pie.
Look at the ghosting. It's asking me do I want this or not. And it looks exactly like what I want. Banana cream pie slice. 17. Okay, yes. Let's accept that. Will it dig out slice and whole? Let's see. Column from example and we want to tell it we just want to look at a particular column. We want to look at this column. So let's highlight that one. And go to from selection. Okay.
Whole. Okay. It's not perfect. Let's see if we can give it another example. Put whole here. Okay. So I had to give it two examples and now it's right. Okay. And that can help us if we wanted to do a filter like okay. We only want to look at whole pies. Let's get rid of that. Let's try something else.
Let's select this column, this column add a column from example. I only selected the last one and the first one. Let's unselect these two in the middle. Let's see if I do 232 space a. Let's give it another example. 38 space s.
Let's try one more. Cause I'm trying to see if it will take the total slice column and then the first character from the first column. It's not finding a pattern. I showed you this because a lot of times you get to know a feature. It helps to play with it. Just see what it'll do, see what it won't do. See where it struggles. And we saw that column by example it helped us with getting a full phrase.
It helped us separate the whole and the slice. But it struggled with something that should have been pretty basic. There it is. Columns by example.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins