Common cleaning tasks - Microsoft Excel Tutorial

- [Instructor] Okay, let's go over some common cleaning tasks when working with Data and Power Query. These are the tasks that I do almost every time, and it's good to cover them right off the bat. I've got the 02_01 SalesData file open and notice that I've got the Queries and Connections pane open over here on the right. Let's close that if it's open so we have more room to work with. So now is the moment of truth. We've got to clean up this data even more. So I'm going to go over to the data tab and I'm going to click Get Data, and then I'm going to choose Launch Power Query Editor. This will let us edit the query we already have here. Now, if I have a date column, I like to move that to the very left. That's just my preference. I like that date to always be the left most column. So I'm going to go and I'm going to find that date column. Here it is, Order Date, and I'm going to right click it and I'm going to choose Move and then to Beginning Option. And the other thing I want to do is remove the columns I don't need. And to do that, I'm going to select the first column I don't want, and that's the company name column. I'm going to go ahead and select that column and then I'm going to hold down the control key and I'm going to select the SKU ID, Call Total, Order and Rank Columns. And with those five columns selected, I'll go up here and choose Remove Columns. Something to notice that's a bit interesting over here, you have this Query Settings pane, and you have the applied steps. As you go cleaning data and choosing options in this window, Power Query will record the steps that you're making, and if you wanted to, you could undo the last thing by clicking the X, but I don't need to do that at the moment. So for now, this looks good. One thing I am going to do though, is I'm going to rename the query to something a little more intuitive rather than just table one so I'm going to select that text and I'm going to just name this 2025 sales. Great, now, to finish up and load this data into Excel, I'm going to click Close and Load, and it's going to run all those cleaning steps, and it's going to move that date column to the first position. It's going to remove all the other columns I didn't want, and this is looking pretty good. Once you get this data into Power Query, you do these initial tasks, you're on your way to working with cleaner data that can be pivoted, charted, and so forth.