Speaker 1: I've trained a lot of people in Excel and Power BI, and I'm still coming across a large number of those people who don't use or aren't aware of some of the key Excel concepts that are really essential to being a good data analyst. There's tables, Power Query, and pivot tables, and even Power Pivot. So I'm going to go through those pretty quickly, but just gives you a sense that they're there and you really need to know about them. Let's go. So let's start with tables. They got introduced to Excel in Excel 2007, and they're just one of the best things ever to happen to Excel. This looks like a table, but it's not an official table. To turn it into an official table, my shortcut is Control T. So Control and T highlights the whole table. And the key thing is to make sure that my table has headers, has a tick in it. And this is now an official table. You even get a new table design menu that pops up. If I click away, the table menu disappears. If I click back in it, the table menu appears again. Okay, so what's so good about tables? And do I have to use Control T? What if I can't remember it? Well, look, under the Home menu, there is a Format as Table option. So that's the same thing as Control T. There's also Insert Table. That's the same thing, and even tells you Control T is the shortcut there. So you've got no excuse about not remembering how to create a table. Right, why are they so good? Well, firstly, there's nice little features. I'm not going to go through all the table features, but structured data is key to doing any good analysis. And even before that, just alternating rows is pretty cool. And if I insert a line, the rows still alternate. Little things like that. When I scroll down, the headings always show, as long as my mouse is inside the table. I can always tell what the headings are. You know, there's nice little things like that. But the real key is that tables, the formulas spill down tables automatically. So what do I mean? Well, let's say I want to join together these two names with a space, okay? I could use the old Concatenate option or the ampersand, but if you don't know about Text Join, let me show you that. So, equals Text Join. I want to put a space between each word, comma. I want to ignore any empty cells, sure. And then I just highlight the two cells to join together. Or I could control click on them, or click on them individually with commas in between. Close the bracket, enter. And there we go. The formula has spilt down automatically. And if I wanted to change one of these, let's say I go into this one, and I say, actually put a colon in between each name. Every single formula changes. And if this table was 10,000 rows long, it would still change. So having formulas be consistent in a column is key. And tables enforce that. It's just really powerful. And I'm just going to change this name to sort of like full name or something, okay? Also, when you're doing formulas, if I want to add up this January column, equals the sum, and I can just click in any cell and do control space bar. That just highlights every number or every value, even if there's spaces. So no more control shift down, control shift down, hit a space, control shift down. Just control space bar. Beautiful, highlights everything. And the table formula is much more meaningful. It actually tells you, it's table two, and it's the column January, 2021. Okay, press enter, and there's my grand total. And I can even change this to start typing in the Feb. There we go, Feb 2021. And you just have to make sure you chop that bit off the end, and there we go. That's now my February. Also, name your tables. So here's table two. If I click in it and go to table design, up in the top left-hand corner, give it a sensible name like table staff. That'll do, okay? And now look at my formula. It's the table staff February, 2021 figures. So it just builds good structure. Okay, so that's tables. As you add new lines, insert rows, the formulas automatically fill down. Much better way of designing robust tables of data, okay? So look into tables. Right, the next thing, Power Query. Hidden away under this data, little tab here, this little section is Power Query. I'm torn whether Power Query is the best thing since Excel started. I think it might be. It beats tables, but tables and Power Query work beautifully together. So what is Power Query? Why do I need it? Why should you know about it? Well, your data never comes out clean. You're very lucky and privileged if you've got nice, clean data that's in a perfect format. Just a little table like this, for example. Look, the names are all lowercase and with gaps in. The months are going across here, which is horrible if you want to try and do some analysis on it. So I'm going to use Power Query to clean up the data, flip the data around, and the cleanup, those steps I do in the cleanup, they're repeatable. I can click Refresh tomorrow and it'll all run again. So you just reduce the laborious time of data cleansing. Okay, so Power Query, if you don't know about it, it can connect to all sorts of things. It can connect to Excel workbooks. If your Excel files or CSV files or things are on SharePoint, I've got a video on that. I'll put a little link, it'll pop up in a second. And if you want to see how to connect to an Excel file on SharePoint or a CSV file, take a look at that video. I'm just going to use this option here from table slash range and it may be called from sheet in your version, if you want a really up-to-date version of Excel. Okay, so what am I going to do? Well, I'm going to pull this data in from table slash range. And now this opens up the Power Query editor window where you can do all sorts of data transformations. You can even add calculations and things in here. It really is pretty impressive. I love Power Query, it saves so much time. It's been around for seven years. Okay, and still hardly anybody knows about it. So here's my Power Query window. I want to uppercase these first two columns. Okay, so I'm just going to click on them. I'm going to say transform and you can just go into these sorts of things like format. And you've got lowercase, uppercase, capitalize each word. Ah, that's what I want to do. So capitalize each word. And see, it's just capitalized them. And the great thing is that it's also added a little step and I can just delete that step if I don't want to do that anymore. It's like self-documenting code, it's brilliant. This formula language in the formula bar here, that's called M, it stands for mashup. And it's a language for transforming your data. Okay, I'd like to merge these two columns together. So I'm going to go right click and then merge. Okay, so don't have to do text join, don't have to remember any formulas. I'm just going to merge the two columns together. I'll then get prompted as to whether I want to give it a new name and if I want to separate it. So do I want a gap? Yeah, I'd like to put a space. You can put whatever you want using that custom option. I'll go a space and I'll just call it full name and click okay. So now we've got full name. And if I wanted to get rid of any extra spaces, I can go right click. There's all sorts of right click options, transform. See, there's the uppercase as well and capitalize each word. Trim, that gets rid of extra spaces. Clean gets rid of in sort of invisible characters and stuff. So trim, okay, doesn't really seem to have done anything but if there were any extra spaces on the end or in the middle, double spaces, it would get rid of them. And then the best thing, one of the best features there is in Power Query is the unpivot function. So you see these columns, they're all the same thing. They're months going across. That's not what you want. You want the month in a column and the values in a column. So I want to flip all that data around. So I'm going to the full name and department column. They're great, they're nice columns. And then right click unpivot other columns. That is what you want to do. Everything else is flipped around and that is now beautiful. So rather than three columns for every item, I've now got three rows for Laura, three rows for Jen. It's flipped it all around and I'll rename this as date. And this as, I don't know, maybe it's the units they're selling, maybe they're salespeople. Okay, and then I can try and turn this into a date. So if I click on this, let's just see if it's clever enough to date. How good's that? It's actually knows it's turned it into a proper date. And actually I want the month end. So right click transform month, end of month. All of this is now repeatable tomorrow. So when that data updates, I can click refresh and all these transformations will happen. Okay, and I can call it clean data. Right, that will do, that is perfect. So here we go, home, close and load, close and load two. So where do I want to load it? Well, I've got a choice and I'll show you both. So I can load it to a table and I'll load it to an existing worksheet and just load it here, okay. So it'll just run away, load, the query refreshes and this little table will magically appear. And there we go, okay. And if I add something new like April's data, let's go April 2021, one, two, three, four, five, six for example, I just right click on this green table, right click, refresh, okay. And there we go, there's April, there's April, there's April, beautiful, okay. And remember Power Query can connect to external Excel files it can consolidate folders of data, it can go off to a SQL database and pull data in, it's just awesome. And Power Query exists in Power BI, Power Query exists in about 10 other products in growing. It's just key to getting data into your reporting. So now I've got beautifully clean data, I can now produce a pivot table. If I go back to this table for a second and say, insert pivot table and just say, okay. The problem here is this. If I say, okay, I want to see my units sold for example, by department, I can put department in my rows. So there's my engineering and finance, awesome, okay. Pivot tables are just beautiful summarization tool, right. It's just great for slicing and dicing your data in a number of different ways. But look, I've got January data, oh, I've got to drag that in, February. How do I do a total? I've got to drag them all in then do a manual add, horrible. Okay, so we don't do that. We don't ever load that sort of structured data. It's already a matrix of data, okay. This is already the months going across, you're gonna need to flip them around. Now, if I go to this table and try and do a pivot, this will be a lot better. So I can just go to the table tools, summarize with pivot table, new worksheet, okay. And here I can just go department in my rows, there we are, and then units, okay. And if I want that by date, I can put that underneath my department and I can put my department in the columns and I can do all sorts of slicing and dicing. And the beauty of pivot tables is you don't break anything. You're not changing any of the source data, you're just slicing and dicing it by row, by column. You just drop in where you wanna handle that. Okay, but if you're gonna do analysis and pivot tables, rather than loading your data straight into this table, instead of that, if I go right click over here, this is my power query, and I choose load to. I'm not gonna load it to a table, I'm gonna load it as a connection into the data model. Now the data model is also known as power pivot. And it's the same data model or a similar data model concept that's used in Power BI. So if you learn these skills in Excel, they're directly applicable to Power BI. And I click okay. It's gonna warn me that this green table is about to disappear, okay. It's still loading, you can see on the right here that it's still loading, but where is it loading to? Well, it's loading to this magical data model, which is capable of holding multiple millions of rows, okay. I've got a video where I do 10 million rows, pull it into Excel. Now Excel, if you don't know, if I go down the bottom, has only got 1,048,576 rows. So if you wanna load one and a half million records, you can't, but you can load it into the data model. Okay, let me show you where the data model is. If I go to data and this green button, manage data model, the first time you click that, you might be prompted to install an add-in called Power Pivot, in which case you'll then get a new tab appear called Power Pivot. So this is the data model, it's opening up. And here would be my 1 million rows, 2 million, 5 million rows of data, okay. And you can also load in other data and helper columns and dimension tables they're referred to as, and build a whole tabular model inside Excel. This is exactly the same sort of experience you have in Power BI. So let's say I needed to hook up my department to a table with department in and a bunch of details, I could just do a nice little drag and drop. If anybody's done access in the past, might be a similar experience for you folks. Or have a calendar table with months, years, quarters, financial years, and just hook my date up to that table, and slice and dice, okay. So that's the data model concept. So what do I do with it? Well, if I just go to a brand new empty sheet and insert a pivot table, and it now says, use this workbooks data model. Okay, because I've loaded data there, and I click okay. It's just a pivot table, but see the icon is slightly different. And it's now connecting to that data model. And again, I can just do department in my rows, and units in my values. And you can start to write DAX measures here as well, which is a whole nother topic. But the beauty of this is, I can just go data refresh, and it'll pull the data in and the pivot table will update. I don't have to load it to a table in one refresh, and then load it into a pivot table in a second refresh. It all happens in one hit. And like I say, the data model, PowerPivot, capable of holding masses of data, and doing all sorts of really cool analysis. So those are my three key features. Tables, you need to know about structured data. Get your data into columns. Power Query can help you if your source data is not in nice structured columns, and needs headings removed, data split apart, joined together, whatever it might be. And then analyze your data using pivot tables, because you can slice and dice, and it's all refreshable at a couple of clicks. So I hope you find that useful. Please subscribe, and I'll catch you later. Power Query