2.11 Add columns - Video Tutorials & Practice Problems

<v Tutor>Okay, so in this sub lesson,</v> we are gonna explore adding columns through the Add Column tab, and we're gonna of course go through a demonstration on adding columns with various techniques. Okay, so what this ultimately looks like, it's quite straightforward. So we have a query that has columns, one, two and three, with the rows listed below, although the rows at this point don't necessarily matter. What we want to do is let's suppose we're going to add in a couple more columns on to this, columns four and column five. Maybe we want to take certain values from column two and column three, and concatenate them together. Maybe to take a first name and last name and build a full name. Maybe we want to multiply a unit price times an order quantity to come up with some type of line total, either way, both of those are valid ways of creating brand new columns. So let's go ahead and get ourselves into our power BI desktop file, that's going to support the sub lesson so we can go into some demos. All right. So let's go ahead and get our sub lesson number 11 power BI desktop file open. Let's go ahead and double click on it. So now the desktop file's open we'll get ourselves into the Power Query Editor, like so, and what we're going to do first is get ourselves into the order date query. So I've highlighted on that. And if we were to scroll around a little bit within the data on this particular query, we would actually go through and find that there is no year on this particular dataset. So our first task is going to be to add a brand new column which contains our year. So how we're going to go about doing that is we are going to scroll back over to the left-hand side and with our order date column being highlighted here we're going to go up to the add column tab at the very top here. And we'll move a little further over to the right under this group here called from date and time. So with the order date column highlighted, once again we're going to click this little dropdown box next to date. And if we scroll down a little bit here we can see that we've got a way to parse a year out of here. So we're going to choose year and say year. So if I go ahead and click here we're going to notice that the very end of this query here we're going to have a brand new column that is called year. So now that we have that brand new column added let's go actually, and give this a better names. We're actually going to call this calendar year. Like so, just so we don't have it called year we'll be a little bit more descriptive about things. Okay? So the next column we're going to add is we're going to go down to the sales query here. So what we want to do this time is if we scroll around in our data set, in fact, this time we don't have to scroll because everything is displaying right on the screen here, in the data preview, is we can see that we have an order quantity and a unit price column here but what if we wanted an additional column here that was called the line total sales. So we're going to go through and add in an extra column in here by multiplying order quantity and unit price. So I clicked the control key, chose order quantity, chose the unit price column. And under the add column, now I can go to the, from number group here under the standard grouping of commands here and I can say multiply, so I can choose multiply. And now it's going to actually go through and multiply those two columns together. So I want to go ahead and give this column a better name. I don't want to call it multiplication because that is not that useful. We're going to go through and call this line total sales. And I'm going to put the letters PQ at the very end to denote that this came from Power Query. So when we move into our data modeling section a little bit later on, we can differentiate between calculated columns and ones that came through Power Query. So I'm just going to go through and do that rename. Okay. So I've got my column renamed here. I'm just gonna go ahead and hit enter. And now I have whoops, that was actually in the wrong spot. So actually what I wanted to do here is go back let's just to, fix that mistake. That is a common mistake. So why not go ahead and just get that fixed. I'm going to change the query back to sales which it was originally here. And what I wanted to do is go to this column here called multiplication actually rename it. So what I needed to do is double click on multiplication here and we're going to call this line total sales and we're going to put PQ at the very interior just to denote that this value is coming from Power Query. Once again, to contrast with what will be a calculated column a little bit later on. So what we're going to do next here is add a conditional column in, and the condition here we're going to do is adding a value into our ordering query. So I'm just going to move to our ordering query that allows us to add a brand new column in here that lets us know if a row is a weekend or not. So ultimately what we're going to do is we're going to say if the day number of week is one then we're going to call it a weekend. And if the day number of week is seven then we're going to call that a weekend as well. So what we're going to do here is highlight on any of these columns here. I'm just going to go conditional column and we're going to get the add conditional column dialog box popping up like so, so here's where we're going to go and configure the logic to calculate whether something is a weekend or not. Okay. So stepping through the final configuration here we added a new column name called is weekend. And the way we calculate it was, we said if the day number of week equals one and we can see up above here that any time the day number of week is one, it is a Sunday. So we say if it equals one, then TRUE, else if the day number of weeks, we're going to go and take a look at the day number week again, equals seven. And if we just scroll down here we can see seven equals Saturday then TRUE as well else, we're going to say FALSE. So once I go ahead and click, okay here we are going to end up with a brand new column on the end here that says is weekend. Now we can see here that the data type has been set to ABC one, two, three, even though we actually wanted to make this a TRUE FALSE data type. So I'm just going to click on the datatype next to here and go down to the bottom and say TRUE FALSE. So now this has actually got the correct data type on top of it. The last method that we want to show you here for adding additional columns is, is column by example. So what we're going to do this time is go over to the product query and we can see that in the product key column there appears to be a pattern here. The first two characters here are AR BA and there's a pattern that emerges all the way down and then followed by a dash and then a series of numbers here. So there's a pattern based on this here. So what we're going to do here is we're going to click on product key and go columns from example. So in add column, the general group columns, from example, here we're going to see that a dialog box is brought up. And what we can see is on the right hand side, is this column number one. And what I can do is go over here, and if I just type in the value that we see in the product key there and type in AR we can see that it is starting to pick the pattern up. So now if I hit enter on AR we can see that it chose the text before delimiter and was able to pick the first two characters out of each one of these and create a brand new column. So what I'm going to do here now is just go ahead and click okay. And now we have a brand new column here called texts before delimiter, which is ultimately what we want we're just going to call this product preferred product prefix like that. And there we go. So now just like that, by using the column by example I have an additional column added into my data model. Okay. So a couple of interesting techniques here back in Power Query that allow you to add columns. That brings us to the end of this particular sub lesson.