Oz du Soleil shows why and how to add custom columns and conditional columns in Get & Transform in Excel 2016.
- [Instructor] We're going back into Get and Transform, and we're going to talk about adding custom columns and conditional columns, this is good stuff, it saves us from having to write formulas, and we're going to see this. Here we have this pie data, what was sold, we've got slices and whole pies, and we would like to do some math on this, we would like to get some equivalents. Since a whole pie is eight slices, how many total slices have we sold? Let's handle this in Get and Transform.
Here we go, we're inside the data set. Data, From Table. So the first thing we've got to do is get an eight by the whole pies and a one by the slices of pie. We do that via a conditional column. The condition we're going to look at is whole versus sliced. Here we go, Add Column. Add a Conditional Column.
Let's call this Slice Equivalent. Okay, EQ is fine. Let's look at the items. Does this contain... Whole? If it does, then give us an eight. Otherwise, a one. Okay. And notice, Get and Transform is taking a guess at what data type we want this to be.
It's assuming it's text. We've got to be deliberate and turn this into a whole number. Now we have to get a sum of the data from Monday to Wednesday. This is going to require a custom column. We cannot do math in a conditional column, so let's do that. Custom Column. Slices. Equals.
We have to do this manually because there is no sum function in Get and Transform. Double-click Monday, plus Tuesday, plus Wednesday. Okay. We've got a problem. We've got a number for some results and a null for others, and that's because Get and Transform treats a null like text, not a number, so we have to turn those nulls into zeroes.
Highlight Monday, hold down Shift, select Wednesday, I'm going to go to Home, Replace Values, Find null, replace it with zero. Okay. Now, Replaced Value, move that above where we added the column, and then... Now we have numbers. Now we can see we sold six banana cream pies, and we now want to multiply that by the eight slices per pie so that we can get a total.
We're going to do that by adding another custom column. Add Column. Custom Column. Move this down a bit so we can see. Total slices. Equals slice equivalent times slices. Okay. Look at that. Ooh, and we can even sort, check this out. Oh, let's go Home, and sort largest to smallest.
Look at that. Apple pie, do you like apple pie? I prefer sweet potato, but anyway, anyway, let's get focused again here. We got the slice equivalent by doing the conditional column, and then the other two columns by doing custom columns. And I'm going to just point out a couple things here. Let's go back to... Where we made the conditional column. So it does not allow us to do any kind of math in here.
Look at this, what if I did do eight times... With eight times what, I can put that in there, but I can't multiply it by that column. So I had to do that math in a custom column. And also, about that conditional column, it saved us from having to write if statements. So there you have it, conditional and custom columns.
- 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