2.10 Add new columns - Video Tutorials & Practice Problems

<v Instructor>Quite frequently</v> when bringing data in from sources, you may not have all the columns of information that you want, for one reason or another. In this sub lesson, we'll talk about adding new columns to our query. So the first one we're gonna do here is I am highlighted on my sales query here. And if I just scroll a little bit to the right, we're gonna notice I have a column here for a unit price, and a another column for order quantity. We may want a third column here that is a calculation of the unit price and the order quantity multiplied together to create a brand new call. So let's go ahead and do that. So the easiest way to do this is to go and find the two columns that you want to multiply together and go ahead and click on the unit price, hit the control key and click the order quantity here. So with these two columns chosen, I can go to my add column tab, and go over to the from number group here and click on standard. And I can click on this multiply here. So if I click multiply, we're gonna notice that a brand new column gets added and it's called multiplication. Which if we take a look at the M code here, it appears that is going through and multiplying the unit price in the order quantity and creating as a new data type, which has the type of a (beeping). So if I go down here and take a look at this it added this new step in called insert a multiplication. And as we previously learned in our previous sub lesson, we can go through and rename this call. So let's just call this line total. So I'm just gonna go and rename it and say line total, like so. And now I have my additional column added into here. All right, so now let's go over to the products query here. So we can see that if I just take a look at the data preview, we've got this product number. And if I take a look at the very beginning of every one of my product numbers, there's the first two characters there are indicating something. So it looks like it's some type of product category or something along those lines. But what I want to do is I'm gonna add a brand new column and just take the value of the first two characters out of the product number. So let's go ahead and do that. What I want to do is once again, make sure that the product number is highlighted like so, and I can go up to the add column tab, and under the from text group, I can click on extract, and maybe I say I want to extract the first character. So I'm gonna choose this. And the dialog box is then gonna ask me how many characters do I want to extract, and I'm going to keep the first two. So I'm gonna go with this, click okay. And now a brand new column called first characters gets added with the first two characters of each one of the product numbers. So once again, I'm just gonna go ahead and rename this, and I'm just gonna call this product code or something like that. So let's give it a name like this. Okay, so now we have our product code that we've created. And finally, let's go and add a brand new column into our product finished goods. So if you recall, that was this one right here, the one that is still using the cryptic name of the file that was brought in. So what we wanna do is let's go and rename this query here. So let's go and call this product finished goods. So I'm just gonna go with this query highlight, I'd go over to the query settings, and I'm just gonna remove this first piece over here and just call this product finished goods, like so. And now we want to do is we want to go ahead and add a brand new column into our query here called finished goods flag. So let's go ahead and do that. And what I want to do here now is go up into my add columns tab, and under general, I'm gonna click on this one here called custom call. So I'm gonna click on custom column, like so. And my new column name, I am going to call this finished goods flag. So finished goods flag, like so, with the spaces. And the value I want to put in here is I want to leave the equal sign, and I'm just gonna put a zero. And now what is gonna happen is I am going to get a brand new column added to this called finished good flags, which is going to be zero all the way across the board. So go ahead and click okay. And now I get this new call. So one of the other things we would just happen here is now the data type for this particular column is the ABC one, two, three data type, which means the any data type. So I wanna actually go through here and make sure we give this a proper data type. So I'm gonna go through, and we're just gonna choose whole number 'cause we want it to match to the data type of the finished good flags in the other product query. 'Cause eventually, we're gonna take these and upend them together. So I'm just gonna go whole number. And now we'll notice that this data type has been changed to a whole number. So we showed you a couple different ways to add some brand new columns with a static value like this here. One where we are extracting some values from a previous column, and then one where we're doing some basic math on a couple existing columns. That brings us to the end of this sub lesson.