Home ribbon: Core functionality

- [Instructor] Now it's time to get hands-on and explore the functionality of these two amazing Excel power tools. We're going to begin with Power Query, exploring the functionality contained within the key ribbons, and then we're going to apply it to H+ sports data. In a prior video, I showed you the various data sources that we can point to. If I was on a computer at a claim company, I could connect directly to other software platforms through Power Query's native connectors. But for the purposes of this course, and so you can learn alongside with me, I'm going to keep it simple and use files that you can experiment with, too. When I go up here in a brand new workbook to data, I'm going to click here at get data, from file, from Excel workbook. I'm going to go into the folder where my data is located, and I'm going to select the file that I want to import. For the purposes of this lesson, I'm going to select H+ complete sales transaction data through September, 2024. When I do, I get this navigator window that pops up here. On the left hand side, you'll see that it points to the name of the file. Now this can be a little bit confusing because you see this folder icon, but this is in fact the name of the file, but then below it, you see a listing of any and all worksheets that is contained within this file. I only have one, so it's shown right here. If I had multiple worksheets within this file, you would see them all listed out, and I would have checked boxes over here to the left. This means that I can select either one worksheet, or I can select multiple worksheets to bring it in to Power Query. When I select my one worksheet that's in the file, I end up with this preview of the data on the right hand side. Now, down at the bottom, I have a few options. I have cancel, which is self-explanatory, transform data, which allows me to go into the Power Query editor and edit and transform the data, or I could go over here to load where I have a couple of options. If I have already connected this dataset into Excel, I could just simply select load, and it brings the data in. However, if I have not yet connected it, I would select load two, select my destination, and voila. As a smart practice, I almost always select transform data so that I can examine my data, shape it, reformat it, and clean it before I ever load it into Excel. So let's do that. Transform data. On the left, you can see that I have a query one. If I had multiple queries, you would see them all listed right here. This is named, not surprisingly, the name of my sheet, transactions to September, 2024. Over here to the right, you're going to see a name, transactions to September, 2024. While this might seem nice and specific, the challenge that I'm going to have is when I bring in new data that goes beyond September, this name is not going to make a whole lot of sense. So it's best that we rename this something that is more meaningful and more flexible, like sales transactions. When I hit enter, it changes the name and all the way over here to the left, you can see that my query has been renamed as well. A small little detail to point out, you're probably noting that I used an underscore. I prefer using underscores to replace spaces, which makes the name easier to read. On the bottom right, you're going to see that I have this field, called applied steps. And down in this area, I have one, two, three, four applied steps. These are extremely important, and I'm going to explain what each of these do. Applied steps are very similar to macros. Macros in classic Excel perform a series of recorded actions. So do applied steps. So we can instruct Power Query to perform certain operations, record them, and then the next time we run the query, it's going to perform all of these steps, again. Source. Source is pretty self-explanatory. When I take a look in my formula bar here, this is written in M Code. This is not Excel formula language. What M Code does for Source is it shows the pathway through my drive, through folders, all the way here to the name of my file. The data here in Power Query is pointing to that file, within those folders, within my drive. Next, navigation. Up here in the formula bar, written in M Code, you can see that this is pointing to the worksheet. However, we have a bit of a problem. Take a look at the data. As you can see, in the first row of the data, I have my headers. It's not really data. And then when I take a look at the headers in the Power Query editor, it's very nondescript, column one, column two, column three, et cetera. I would like for the first row of my data to actually be declared as my headers. Well, I have good news. Power Query is smart. It detects that issue, and it's going to promote the first row as headers. There we go, exactly what we want it to do, but we still have a problem. In each of these headers, you'll note that I have these icons, ABC one two three, ABC one two three, ABC one two three, generic formats for data. Well, I want the numbers to be formatted as numbers. I want the text to be formatted as text. I want the dates to be formatted as date, and I want total cost to be formatted as currency or numbers with decimals. Well, again, I have good news. Power Query is smart, and it's going to detect the data types and change the types accordingly. Under the applied step change type, now take a look at what has happened. Total cost has changed from ABC one two three, to decimal numbers. Dates have changed from ABC one two three, to date. Salesperson has changed to text, and order number has changed to whole number. In the event that I don't like what Power Query did, all I need to do is select this icon and pull the format that I want. Now, let's explore the home ribbon. The first area that I'm going to show you is sort, but before I do this in Power Query, I want to illustrate how this works in classic Excel. Imagine that Jocelyn imports the sales data into Excel, every month, but this system imports the transactions from oldest to most recent. Jocelyn has to manually go over here to date and either go over to this filter and select from newest to oldest, or go up to the data ribbon and sort it the same way. Jocelyn manually clicks the sort button in Excel and gets the data in the form that she wants. But what if she could do this automatically, without having to click buttons, every month before she even imports the data here? Let's go back into the Power Query editor. I'm going to move us all the way here to the right and focus on this column that says date. Now, I'm going to go to my home ribbon, and over here to where you see sort. Sort ascending, sort descending. This is the one we want, from the most recent date to the oldest. Done, that's it. And when we go over here to applied steps, you can see that a new step was added, sorted rows. Now unfortunately, the way that applied steps work within Power Query is, it gives it a default name. It doesn't tell me what rows it's sorted by, so what I'm going to do is do a right click here and select rename. I'm going to rename it something more meaningful, sort most recent to oldest, and hit enter. So the next time that Jocelyn imports and brings her data into Excel, this step will automatically be applied. If I don't like the step that I just added, I can't do control Z, but what I can do is put my cursor right here on the X and get rid of that step. It takes me back to where I was. Let's take this a step further. On the right hand side of my data, I have three columns, item cost, number of items, and total cost. Maybe we decide that we don't need item cost and number of items. It's just extra data that I don't have any use for. How can I delete these two columns and record the step so that the columns are removed, the next time that I import a new month of data? Well, I'm going to go over here to where it says manage columns. I'll select choose columns. Here, it gives me a list of all of my columns. I'm going to deselect the two that I don't want, item cost, number of items. Click okay, and just like that, it removes the two columns and records the applied step. If I wanted to go up here to remove columns, this applies the same kind of operation, and to be more thorough in my work, I'm going to rename this step. Not remarkably dissimilar, if I point my cursor over here, managing rows lets me adjust the rows. You'll note that at the top of my data, I have five transactions for the month of January, one, two, three, four, and five. Let's say that I don't want that information. I can go up here to remove rows, remove top rows, bottom rows, alternate rows duplicates, blank rows, errors. I'm going to select the top rows, specify how many rows to remove from the top. I'm going to put in five. When I click okay, those five transactions are gone. Right click, rename, removed January transactions. The home ribbon obviously contains a number of other commands, and we'll explore that functionality in future lessons as well as the other ribbons. This is just the beginning of navigating through Power Query. There's a whole lot more that we'll cover in the transform and add column ribbons.