2.9 Rename columns and set data types - Video Tutorials & Practice Problems

<v ->Often when we get a data source</v> or connect to a data source, the column names and data types are not quite what we would like them to be. In this sub lesson here, we're gonna talk about fixing up column names and setting appropriate data types. So I am right now highlighted on the products query in Power Query Editor. And I can go across in my data preview pane and see that there's a number of columns here that don't really have very good names on them. So I've got this column here called pnum, which looks like it's probably some type of product number. Pname, which is some type of product name. And then I got a number of other columns here that don't have any spaces between the words. And that can be a little bit problematic when it comes to end user reporting or consuming things later on. So you just want to make sure you're making good choices for your names. So let's go ahead first and rename these columns right here. So what we can do, there's a few different ways of doing this as I can double click on the column itself and it will turn blue, so I can do the rename. I could right click on it, and down near the bottom there's a rename function here, which basically is the same thing. Now the column turns blue. And at this point I can go and rename this call. So I'm gonna call this product number. Product number like so. And the next one, I wanna go ahead and change this to product name. So I'm just gonna go like this and say product, product name like so, hit in Enter. So now I've got these columns lined up the way that I want. And let's suppose now that I also wanted to, maybe just add some spaces into this finished goods flag. So I can just double click inside of the column and in between each of these words, so I can go finished goods flag, like so. That just makes it a little bit friendlier for our end users. So the next thing we wanna do here, I'll just show you how to do the column renaming on those first three there. Because once you learn how to do column renaming, it's the same across all the different columns you may want to do. So there's no point watching me redo 14 or rename 14 different columns in this data source. So the next thing we wanna do is, let's go ahead and make sure that each one of these columns has the proper data type. So I'm gonna just go through each one of these columns, one by one, we'll actually do this here. I can see that this first one here is a text data type. The product number is a text data type, and the way you can tell that is this ABC rate here. So if I click on that little box right there, I can take a look at the data types and actually change them if I want to right here. And I can go down here and see that ABC, that symbol indicates this is a text. If I want to change it in to one of these other ones, I could, if long as it was a valid conversion. So going through the rest of the columns here, product name is set up as a text, that's great. The finished goods flag is set up as a whole number. So if I click on that I can see it is a whole number, which I'm fine with. Going across the column that's text, which we're good with. We start growing a little further across here. So whole number, whole number, then we move into these columns here that are our cost values. So what I'm gonna do here is I'm just gonna click on this, data type symbol right here. And I can see this is set up as a decimal number, but what if I wanna go ahead and make this a currency? So I can go ahead and click currency. And now I can see that this now is rendered as two decimal places. I can go over the list price and do the exact same thing to make sure its data type gets set the way I want, go across. We're good with text. That is fine. That is fine. Then we get to this column right here, which is a start sell date. So when do we actually start selling this product? And it looks like this actually set itself up as a text value for some reason, but we want to actually make this a date. So what we can do is click on this here and go down to date. And now we can see this converts itself into a date. One of the reasons that setting the data type is so important, is that a lot of the downstream transformations you may wanna apply to your data, will be dependent on the type of data that is chosen. So I can see here that when I clicked on this start sell date two, which I just turned to a date. Now I get these date filters that are showing up like so where I can actually do filtering based on the actual data itself. So it gives me a whole lot of different options. If I go right to next, to the one next to here, I can click on this category name, which is ABC, one, two, three, I click the dropdown box next to it. And now I've got filters that are for text-based values. So with that, making sure that right off the bat, as you are getting things brought into Power Query Editor, you do a few things very quickly. Number one is, as we've talked about previously is, remove columns you don't need. Number two, is remove or filter the rows, you don't want your data sets as well. Number three is making sure you get your column names set to names that make sense to you. And number four is making sure you get the proper data types set up. Because you're gonna live with the names and the data types all the way through the rest of the transformations you do, and ultimately out to whatever your target is that you're delivering your data to and users will then have to use it there as well. Okay. That is it. And that brings us to the end of this sub lesson.