Excel Hour - Learn to Excel with the FP&A Guy

It had too many issues to sort out.108 in Vegas. We got someone from Vegas, Poland out there like Nick. Feel like I am quasi coding. Yeah, it does feel a little bit that way. We're just from the Philippines, so we got a great group out there, a lot of different people. Appreciate y' all joining again. On my screen right now there's a QR code. You can go to my website, the FPA guy. There's an Excel resource section and you can download the zip file for today's courses. Always. I try to make these hands on so you guys can follow along while we're waiting. We're still not quite to the top of the hour, so we'll do another Excel joke or two here. Does anyone know how Excel users flirt? Yes, you heard that right. How do Excel users flirt? They use. But we will get started here shortly. But in Mesa right now. Yeah, that is. We got Sweden representing Stephen there, Joanna from Spokane. I'll be in Washington in a couple weeks. Yes, Carlton, I'm based in Mountain Time. All right, we got about 20 of you out there. Again, I'll just repeat, go ahead and use the QR code if you haven't already downloaded the file. We'll give it another minute or so and we'll get started here. As always, this will be interactive, so feel free to put your questions in the comments. I'll do my best to monitor them as we work through Power Query. This will be a very entry level introduction to Power Query. We'll go over some basics just to help get you familiar with it because I really believe it's a tool. If you're using Excel, you're going to benefit from knowing Power Query. I haven't found anyone that would tell you otherwise. Now, how much you'll benefit depends on your role, but especially as a finance professional. So the QBR is right on my screen there, Bill. It should. Oh, hold on. I just realized I'm not presenting. I thought I was presenting. Give me one sec, I'll bring it right back up. There we go. You should see it on my screen now. Sorry about that, Bill. I added up my screen. I hadn't shared. That was totally my fault. So if anyone needs the file, go ahead and download it. Welcome Lulia from New York. And we'll give it just another minute or two here and then we'll jump into things. So we'll do one more joke, give people an opportunity to download the file and then we'll get started. Does anyone know what a spreadsheet says when it apologizes. It says, I'm so sorry. I don't know what happened. I must have entered the wrong cell. These ones are coming from Chat GPT. Not my favorite, but you know, we tried. Alrighty, we'll give it just another couple seconds here to download the file. Also, even if the code's not up, you can go to my website, the fpnaguy.com/resources, there's Excel files and there will be a download button there to download the zip file. There should be three files in the zip file. There should be an Excel file, a CSV and a PDF that we're going to use for today. All right, well, why don't we go ahead and jump into things. So if you don't know me, if you haven't attended one of these sessions before, my name is Paul Barnhurst. Name of my business is the FPA guy and that's the nickname I go by, you know. I have an MBA and a Master of Science in Information Management. I spent roughly 12 years full time in FP and A in FPA. Before that I worked in procurement for the government writing contracts. I've worked for a number of global brands, Digi Cert, American Express, Solera. Today I do a lot of content creation in fpa. I'm a trainer. I do corporate trainings, on demand trainings, virtual trainings, love training. And then I host three podcasts. So if you're not following them, I'd love it if you'd follow them on your podcast of choice. Subscribe to them and leave a review. I have FPA tomorrow, all about Financial planning and analysis, Future Finance, which I host with Glenn Hopper, that's all about AI and technology and finance and then Financial Modelers Corner, all about financial modeling. So I have something for everyone in finance. So go ahead and check those out if you haven't. And then let's go ahead and cover what we're going to talk about. Today we've done an introduction to myself. I'm going to do a brief introduction of Power Query and then we'll walk through some exercises. Oh, we have the one, the only Oz in the house today from Excel on Fire. He should probably be giving this training, but welcome Oz. Excited to have you here. So the way to think about Power Query, and this is a kind of a technical definition and then we'll talk about kind of in a more simple term of what it is. Basically if anyone's used SQL or they've used an Extract transform load tool, that's what Power Query is for. Microsoft. It works in Excel, it's in Power bi, it's in Azure, it's across the Microsoft tech stack. And basically it allows you to connect to different data sources using connectors, and then you can clean and transform that data using a very intuitive interface. For the basics is it gets more complicated to use what's called M, which is a coding and. And then you can load that data into a worksheet or into your data model, like if you're in Power BI or Power Pivot. It's a fabulous tool for cleaning up your data. A way to think about it here is Power Query sits in with all these different tools and it takes your data, whether it's from a database, the cloud, a flat file, wherever it may be, and it allows you to work with it in different tools across the Microsoft tech space. Back so as I mentioned before, and I'm not going to cover this in any real detail, we've kind of talked about it, three purposes. Extract the data. So connect to it and pull it from whatever system you need, whether that's a flat file, an Excel file, a database, the cloud, a PDF, transform it, perform steps to clean it up, then load it to where you need it. As simple as that. Yes, J.K. the session will be recorded. It's being recorded on YouTube, on LinkedIn, and it's also available on demand on Streamyard, so on all three of those platforms. So here's some of the benefits of Power Query. I'm not going to read through all these because I want to jump into the file, but, you know, automating things is huge. Integrating, handling large data sets, you know, you often hear, hey, Excel can only handle 1.2 million records. Well, yes, the Excel grid can, but you could bring in millions upon millions of records in Power Query, summarize them, work on them, or bring them into a pivot table so that they're summarized and analyze them. So you could work. I've seen people do 10, 15, 20, even heard cases of 50 million records into Excel. Obviously, at some point you really should be using Power Query and other tools depending on the size of your data. But that million row limit doesn't apply to Power Query. I've done a lot more than a million, many different times. And one of the best things is it's for the most part a low code, no code tool. Yeah, can you learn M code? Can you do coding? For sure. And more comp. More complex you get, the more you may need it. But for the basics, you don't need the code. All right. So that covers the PowerPoint for now. We're going to jump into Excel. So go ahead and open with me the file called Power Query Overview. And I just meant to enlarge it and I closed it. So let me reopen that. If anyone has the joke, feel free to put it in the chat as I reopen my file. It's what I call an ID10T error. If you don't know what that is, write the letters ID the number 10, not spell it, but the number 10 and the letter T spell the word. That's what I just did. All right, try number two here. So in the file, we're going to start with a simple table and just show some of the basics of Power Query and kind of walk you through the interface. We'll probably spend about 15 minutes there and then we'll walk through a few examples. All of this that I'm covering today is covered in my course I have on Power Query and more. And you can find a lot of great resources on the web. Oz, which is attending, has a lot of great videos on his channel and there are many, many others out there that you can learn from. No shortage of resources. So where we're going to start is show you where Power Query is in Excel. You won't see the word Power Query in Excel. At one point it did use that name, and in some places you still see it. But within Excel, if you go to your data ribbon, you can go there with me. You have a Get and transform data menu pane in this queries and connections. So this queries here, if you click on that and it will bring up a query pane, which later today you'll see. In fact, there's even some connections in there now for some stuff been loaded. We'll, we'll load it again. But that's the first thing. The queries pane will show stuff that's in Power Query. In fact, if you right click on them, you could delete them if you wanted to. And we'll go ahead and do that for now and we're going to reload them later. So I'm just going to right click and delete these two files. All right, so we're now going to come back to this first file here. And then you'll see here there's this get data button and then there's a bunch of kind of shortcuts or, you know, menus on the ribbon. There's also, you can use the keyboard shortcuts for these as well. But you have loading data from text Loading data from the web from a table or a range. You could even it has from picture. You could load something from a picture. It will scan it and try to output that into your Excel Grid uses OCR scanning. You can see recent sources, existing connections. In addition to that, there's this button here called Get Data. Your menu may look different from mine. The menu is going to vary depending on whether you have a personal account, a business account, or an enterprise account and what license you have. So the the more of an enterprise license you have, like if you have E5, you're going to have more connectors here. If you look at Power Query in Power Bi, you're going to have a different list of connectors. So here you can see you have from file, I could load something from a workbook, a text file, PDF, a folder. Then you have from Database, an SQL database, Microsoft Access, if anyone's still using it. You can load stuff from Azure here. You can load from the Power platform. So you can bring in stuff from Power BI or Dataflow. And then it has other sources. If you have an E3 or E5, you should see Salesforce as an option. You can load things from there. You can do a blank query and then it has some other things. We'll talk about these later. How to merge and append your data. You can launch the Power Query editor here. You can go into your query options and your data source settings. So before we open up Power Query, I want to show you in this file I've given you a list and there may be some that have been added. This is from a few months ago. I haven't updated recently, but it's a list of all the Power Query connectors for Excel with a link to Microsoft. So if you click on the link, it's going to take you to an article about it to provide some information capabilities supported. How you would connect walks you through an example. So if you want to learn about all the connectors that it, this is a great resource to get started. All right, well, let's go ahead and jump into Power Query. What we're going to do is I've already converted this data to a table. If it wasn't a table, I would recommend you turn it into a table. I'm a big fan of using a table for pretty much all my data, but let's go ahead and start here. Under data, there's two places we can load this into Power Query. I can click from table range and it's going to open Power Query. Or I could go from get data, other sources and click from table range. Or I could use the shortcut. I could go alt A and then you'll see from table range pt any of the three will work. Choose one of them and what's going to happen is the Power Query Editor is going to open. Notice here it calls it Power Query. Notice here it says get and transform data. So they've used some different names over the years. And so a few things to note right off the bat. I can no longer work in the Excel grid. I cannot work in Excel. When I open Power Query, I have to close Power Query to work in Excel. So it is its own application within Excel and you cannot work on both the same time. It's important to remember that it'll probably drive you crazy a few times. It does me from time to time. Now let's walk through what we have here in Power Query. So first up on the top we have the menu bar similar to Excel. You can see a file, a home, a transform, add a column and view. We'll walk through each of these in a minute here. But there's different operations. You could perform transformations on your data. Yeah, yeah, of course. Bill, let me do that real quick. So just Bill is asking how we get into Power Query. He got dropped for a minute. So let me. What I'm going to do is I'm going to close this and I'm just going to say discard for now. You guys can stay in there. You can do this with me. So what you can do is go to the data button here, Bill, and just click. Probably the easiest is click on from table range while you're inside your table. That's going to bring you into Power Query. So we are talking about the first section, which is our menu bar here in Power Query we have our menu bar. Next you're going to notice here on the left side there's this queries section. If I had 15 queries, it would show all 15 or 50 or 100. I've seen 100 plus queries in a file. Now, I wouldn't encourage necessarily that many, but you can load many, many queries here and it will tell you how many you have. Little icon and it will give you the name. The name in this case is the name of our table. Whatever we gave our name, we gave to our Excel table. The next section is, we have a formula bar right here. You could see it. You could see this little drop down. It shows the. That's the M code that I talked about. So that's this second Step called Change type under Applied Steps. We'll get there in a minute. But that's the formula bar. Next we have basically what I'll call the grid. You can kind of think of this like your Excel grid. It's going to show whatever query the data for your query. It's going to show first, you know, 100 or thousand. You could scroll down up to a thousand rows. It will show each column. You'll notice each column is going to have the title name the data in each individual row. So it is again in a column row format. And we'll talk a little bit more about data types in a minute. Next you have this section called Query Settings. So there'll be a couple things here. First, you're going to have the name of the query and you can rename it. If I didn't want January Sales, I could just rename this to be Sales if I wanted to. Or I could name it January. You could name it whatever you want there. Then here, similar to vba, it's going to show all the steps it does. This is the automation part of Power Query. So the first step you'll see is right here. It's called Source. And if I click on it, you'll see in the formula bar it shows what it wrote. It said Excel currentworkbook, you know, the parentheses and then it says the name equals January cells. Bring in that content. Then because of the way it's set up, it automatically changed each of the data types. One thing that's different. So first you, you have these applied steps. They'll always be applied every time you open something, Power Query or refresh it. In Excel, it's going to run every single one of those steps. So that's the great part about automating something. So if you brought in a file each month, you could refresh this and it's going to bring in new files and we'll talk about that. So that's the applied steps. Yeah, there was errors in the month, Bill. That, that was deliberate. I'm not, I'm not surprised that you saw an error there, Bill. You'll notice this isn't a real date. It had some confusion and I'll show you why here in a second. So now what I want to do is I just want to spend a few minutes and just walk you through what each of these different menu bars are for. So the first one here, file you can close and load, that will automatically load your data to an Excel table. Close and load two will give you different options. And we'll show that in a minute. Discard and close basically means ignore everything I did just exit out of Power Query. Then you have your query options and settings in general. When you're first learning Power Query, go ahead and go with the default options. But there are some settings in there that you could modify. So if I bring those up, you can see there are global and current workbook settings, how you load your data, your security, your privacy. We don't have time to go through those in detail as though we only have an hour, but you can go ahead and check, you know, kind of check those out. As a general rule, like I said, when you're starting out, default should be fine. All right, so that's the file menu. Then you obviously have the help. You have this export template, which I'm going to admit I've never done, so I'm not going to cover it now. Under home, you again have the close and load options. You can refresh your preview. You know, here, if you click Advanced Editor, that will bring up the actual code. So M code is the language. Again, if you wanted to see what it actually wrote, it always starts with the let statement. It always ends with in. And your last step. And so that you could go in and see that that's the advanced editor you have the ability to manage. And then, you know, here are just some basic changes that are common. Do I want to choose or remove certain column columns? Do I want to keep or remove rows? Maybe you have a total at the bottom you want to get at. Do I want to sort my data? You know, split columns, group by merge and append. Append is combine different files together. Merge is a join, basically joining different tables together. So think of it as maybe xlookup or index match or vlookup, whatever you prefer. On steroids, you can create your own parameters. We won't talk about that today. And then you could see some things for a new query. So that's the home menu. The transform is where you'll. You can perform transformations on a column. So you could transpose your data, count your rows, you know, fill. If you had blanks, you can fill down and fill up data. So, so example, if this said product one and none of these others had products, I could fill the data down or up. Very useful. You could pivot and unpivot your data. We'll show an example later. Split columns by delimiters, by numbers, and many other things. Perform math operations. Simple example, I'll just show one here real quick is, let's say I highlight well first, let me change this null and I'm going to show you. So if I come in here, actually I'll show later. Let me hold on that. But you can perform math operations. So you can highlight cells and multiply, subtract, divide and. And many other options. You can do things with date and time, if it's a date column, and transform transforms the existing column add has many of the same operations, but it adds a new column. And we'll show examples later. And then last, I want to show you the view. The view here is really helpful. You could do a couple things like click on column quality. I could see the quality of my column. Notice, oh, 17 are empty. If I had an error, I could see that I can also profile a column and so it counts how many in the column there are, how many are distinct, how many are unique, you know how many are empty, what's the min and the max. You can even do a column distribution. And so that helps you kind of quickly profile your column. You can also go to the advanced editor and then last year, this query dependencies gives you a map of all the queries that are in your workbook. You could see here, current workbook has January, but if I had linked out to the web or Salesforce or an Access database, they would all appear here. And I can see a mapping of all my connections. Sometimes it's a little quirky the way it works, but it gives you a really good idea. All right, so before we load some more data, I want to just show you data types real quick so you'll notice the second step. It said change type. Every column has a data type. So this is different than Excel. It's really important you understand if you click right here on this abc, you'll see the different data types. A column can be a display, a decimal number, a currency, whole number, percentage date, time, date, time. So these are all the time ones, text, true, false, or binary. And you could perform different operations on each of these. And you will need to understand the different data types because if it's not the right data type, you can't perform certain math operations or data operations. The prime example would be go to column two with me, change this to date, and you're going to notice what happens. Right now, notice under transform, this is grayed out the date and time. When I change this to date now, all these date operations become available. It also gave me an error because that wasn't a date. And so I wanted you to see that error there. When I changed it before, it wasn't showing it as a date. So you'll notice, you go back here and look at your column quality. It should, it's still, I guess it's going off the original. But if we had the error originally, this would show 17% of error. So it goes off the original data before you made a transformation. But if you came in with an error, it would show right there. So that's the interface. I'm going to go ahead and close this now. We don't need to load it, so just click the X and we're going to discard it. Here you're going to see the different data types and just a brief definition of each of them will be in your file. So I recommend familiarizing yourself with that. And I'm going to pause here for a second, grab some water and just see. Before we move on, any questions from anyone on what we've covered so far? All right, if there's not any questions, we're going to practice loading some data in Power Query just so you get familiar. And we're going to use that data later on. So I want everybody to go to the tab called January with me. Click on the data, say from table range, load it to Power Query. We're not going to make any transformations at the moment. We're just going to load it. So once it's here, you're going to click close and load, Close and load to. So there's a funky line that overlaps most of Ribbon and Power Query. I I Is anyone else seeing? I'm not sure. I'm not seeing anything, Bill. So I'm not, I'm not sure what to tell you. Is it on your computer? On mine. And we'll wait for that comment. I'm going to keep going, but. So what I'm going to do now is close and load two. And this is what I talked about earlier. You can load something directly to a table, to a pivot table, report to a pivot chart, or only create a connection. For the moment, we're going to only create a connection. What that means is the data. I'm not sure why you're getting a line. Bill I After we're done here, I could look at it for a minute, but I don't know why you would be getting a line that's overlapping the ribbon. I've never seen that before. If anyone has any ideas, throw them in the chat, but I'm not, I'm not sure what would be causing that. So what we're going to do here for the moment is we're going to go only create connection notice. Now we have this January data connection. Only if we hover over, it gives us a preview of our data. If we click here, we could edit it, take us back into Power Query. We could delete it, we could refresh the data will have a number of different options here for the moment. What, what I want to do is just have you now load February notice. January is the table notice, February is not. So go ahead and click anywhere in February's data. Click from table range. The first thing Power Query is going to do is, is force you to convert it to a table. That's why I'm such a big fan of learning about tables. Power Query is going to use them all the time. So we'll click. Okay, it created the table. Now it's loaded it into Power Query. And notice this one is called Table three because I didn't name the table when it created it. So here I'm just going to go ahead and call this February Underscore Data for the time being. That's why I always recommend you create your tables first before loading the Power Query. But I wanted to show you what happened. That way you can name them and they'll come in with that name. So go ahead and click close and load two. And again, we're going to only create a connection and we're going to use this later on. We're not doing this just so you get to do it a bunch of times. Now I'm going to ask you to load all three of these tables. These are our lookup tables that we'll use when we do lookups in Power Query. So just real quick. If everybody can just load these real quick, I'm going to do it myself. I'm going to go fast here because you've seen this a couple times and I don't want to bore you. So I'm just going to do these as quick as I can. But I want you to get the basic idea here. So it's going to load this one only create a connection. We'll load this one. And if anyone has questions while I'm doing this, you know, feel free to ask. I know this takes a minute, but please do it on your end because later on we will use these. We'll use it for appending and merging data together. All right, so we've loaded from a table, but we have a lot more options than that. So let's show some of the other loading if you notice. Right, you come right here. Next we're going to load a CSV file and I'm going to delete this table that's here and we're going to redo it. So if you have that table there, go ahead and delete it. And what we're going to do now is in the zip file there is a CSV file called January Cells. We're going to load January Cells into Power Query. We're going to export it as a table into this sheet. So wherever you save that CSV file is where you're going to need to go on your machine. But you could click right here from text CSV. I'm going to go to where it's located on my machine. That's going to be different for each of you. So I'm just going to come here and you'll see right there, January Cells. I'm going to go ahead and click Import. So wherever you have it saved, click Import. And what's going to happen is it's going to take a minute. It's saying, hey, connecting, please wait. And then it's going to bring up a preview of the data, whether it's an Excel file or a CSV. You'll get a different screen here. They're treated different different by Excel. In the interest of time, we're not going to go through each one of them, but just know they'll be a little bit different with the CSV. First thing you're going to see is the file origin. As a general rule, the default CSV file. It's going to get it right on this, I think only once or twice have I ever had to change the file origin where it didn't pick up what type of CSV it is. Next you're gonna have the delimiter. Generally it'll be comma one. I see that sometimes people use when they're worried that their data has commas, so they don't wanna use a comma separator is a stovepipe. That's one of the commands you can use just the little line that's below your backspace. But in this case it picked up the right delimiter and it based it on the first 200 rows, which in this case there's only 460 rows in the data. But you can choose it based on the entire set or you can say, hey, don't detect the data types, I want to do it myself. We're going to leave it all default and then you're going to have two options. You're going to have you could directly load the data. Now if you Wanted. I always bring the data in and transform it. So I'm going to click transform data. We'll bring it into power Query. I would make any transformations I wanted here. Like if I didn't want a column, I could remove it. Any changes you make does not impact the underlying data. They're only made in the editor. So there is no risk of ruining your underlying data. And that's important to remember because sometimes people are worried, well, what's going to happen to my data? So I've now brought in January sales as a CSV. I'm just going to go ahead and close and load and in this case we're going to load it to a table so you can see what that looks like. So right here, select how you want to view this data. I want to view it as a table. I'm going to put it in B8. I'm going to say existing worksheet right here, I'll select B8. Click OK. And there we've gone ahead and loaded a CSV. If I was to load an Excel file, the process would be very similar. I don't have an. There wasn't an Excel file in what I sent you, but let me just show you an Excel file so you can see it real quick. So from an Excel workbook, I could come in here and here, here's an. Let's see, we'll just take this file here just so you can see what it looks like. If it's a cloud file, it may have to download it. So it'll take a minute. If you're using OneDrive. And I just want you to see the difference here between a CSV and an Excel file. So it's taking it a minute. It's thinking, it brings up this navigator and it will bring up the different objects. If there's tables, it will show those. So I could click here and see, oh, hey, what this evening calendar looks like. What's my morning calendar in this case? And I could bring as many of these in if I want. Select multiple items, I could bring them all in. So the difference is with Excel is it's going to show your sheets and it's going to show any table objects and you could bring those in as well. You also have the ability. We're going to do a PDF, we're not going to this, but one of the most common is to bring it in from a folder. Let me share that use case because it will be really important to understand how many of you and you could throw it in the chat if you do this, but you think in your head, how many of you have ever had a monthly report where each month you copy and paste the prior month to the bottom of your Excel worksheet? I'm going to guess if I asked, by a raise of hands, if everybody can see each other, most hands would go up. You've either done it or inherited a file that's done it. Very common from folder allows you to just drop each month's file in there. And as long as they're structured the same, you could just hit refresh and let Power query combine them together. So it's, it's avoid that copy and paste that manual process. It's really pretty amazing. Now let's do another load before we move on. So here, here's kind of an example of what data from a PDF looks like. This is a resource for you. We're going to load a PDF view, then we're going to move on to append. We're going to do a join an on pivot and probably a few basic transformations. So in the file you got today, there was a sales rep data PDF. We're going to go ahead and load that PDF. So we're going to go from file from PDF and I'm going to come find where that's saved. So wherever you have it saved on your computer, go to that folder and we're gonna go here and it's gonna be the one called cells Rep Data click import. Now this is an example. The data is gonna be pretty clean depending on how they created the PDF. It can be a mess, maybe a lot of work to clean up the data. Sometimes it might do a really good job bringing it in. Notice here, it gave me an object of a page in a table. So it looks like a table was saved into the PDF. If there is a table, that's usually ideal. So we could see really clean. Here I get the sales rep name, the sales target, the sold amount. The page is also really clean. In this case, you could select either. I usually like to use the table if it's there. If there's multiple items you want to bring in, you can bring those in. So I'll click transform. Give it a minute here. And now it's brought it into the power query editor. So we could see it's named Table 0. It gives the name there. I'm going to rename that to cells rep. If I can type, I think my. There we go. Have my cap blocks on data and you could put spaces between them. You could do underscore if you want, whatever you want to do. So we could see. I have my three columns. Notice though, I want to show you one thing with these. When the CSV came in, notice it performed three steps. There's the source data. It promoted the headers because the headers in this case showed up on row one and it said, oh, those need to be promoted. And then it automatically did the change type. You can turn that off if you want and select whatever type you want things to be. There's reasons to do both. That's something that, you know, kind of varies a little bit of preference. I tend to leave it on, but I'll often sometimes change some of them if I don't like the data type it selected. Again, notice here, in this case, it did four steps. So it has the source file, then the navigation. The navigation is that step where we told it we want the table. If I had told it I want the page, it would have the source of the page here. Then it promotes the headers. Then it automatically detects the data types when you bring something in from a folder. You'll have even more steps here. And these gear icons allow you to go into the steps. So let me just show you a couple things here. If you click on the gear icon, I could go change the path. So if somebody sent you a file, you might and they were using some kind of shared drive or something and they named it differently, you might need to go in and edit the path. So you can do that. Right there it says, hey, what type should I open the file as? Navigation again has the gear icon. So you could go back and say, I really wanted this to be page 001 instead of table 001. If I do that, you'll notice it re performs some steps because it said, oh, I got to promote the headers and and redo that with the new table. So not a good idea to change it in the middle, but I just want you to see it. So I'm going to delete those steps. Click on the X and I deleted those last two steps when I made this change here. So I can do that. I can also come into this one here and it says promote the first row. You could promote all. All types or text and numbers. I usually don't have to play with that. But just so you can get an idea, those gear icon allow you to go back to steps. So we've brought in some data from a PDF. I'm going to go ahead and close and load. Close and load two and I'm going to Bring it into the, into the A table here. I'm going to load it right here in B7. So I'm going to go table existing worksheet, select B7, click. OK, so I'm going to pause here. We have about 20 minutes left. I want to see if there's any questions so far. All right, you know, if there's any questions out there, please let me know. So next I want to show you how to append files. So remember we loaded January and February. We're now going to combine January and February together. So what I want you to do is on the data ribbon, if queries and connections is not clicked, go ahead and click it. Make sure you're showing this on the right hand side. I'm going to expand it out a little bit. I'm going to come to January and I'm going to click Edit. That's going to bring me into the Power Query editor. There's a number of different ways you can, you can get in to do this, but I like this way. I am now going to append data together. So I'm, I'm on my January query right here on the home menu. I have this append queries option and I can append queries in an existing query or create a new query. In this case, let's go ahead and create a new query. I'm going to click new and it's going to bring up the append dialog box and say concatenate rows from two tables into a single table. So basically combine them together. Or I could select three or more. So here if I'm doing two, I would just select my second table, which is February. If I'm doing three or more, it tells me, hey, what tables do you want to combine? Well, in this case I only want to combine February, even though I selected three or more. If March was here, I could add March. If April was here, I could add it and it will append them together. Now when you're appending things together, as a general rule, you want to make sure each column has the same data type and they're the same columns. And if they're not the same columns, like if something spelled differently, like capitalized differently, a space in a different place, it will treat that as a different column. And I'll show you an example of that here in a minute. So go ahead and click ok. And it now created this call, this query called append. I'm going to go ahead and change it to cells. You're going to notice as one step all it did is it ran this function called Table combine and it combined my January and February data. And if I scroll down I can see that this says February. But what would happen if this was named differently? So I'm going to come in here to January data and I want you to go order underscore id. So we, what we did is I just right clicked here and said rename or I hit F2. I usually will hit F2. That allows me to rename it. So I named it something new. You could see it created a step here called Rename. You could see here what that step did. But now let's go down to ourselves. Query and notice I now have two order IDs. I have the new one I named and I have a second one here. And for January it's blank. And for February it's filled in, but it's blank over here because it said these are two different columns. So you need to be really careful with your naming. So let's go back up to January, delete our renamed columns and then we can come back to our cells and notice it fixed refreshed it and said, oh hey, it's all good now. So that's the append queries function. So what we're going to do now is we're going to load January and February right here for the moment. So notice I made a mistake there. I loaded it into a new table, a new sheet. If I don't want that, I can right click here and say delete and get rid of that. And I can always come right over here, right click say load to and say I want you to load it to a table, but I want it in the existing worksheet so that when I deleted that sheet in that table, it didn't change anything in the underlying data or the query. It just changed where it was loaded. It changed it from, hey, it's loaded in this, the sheet to a connection. Then I said, no, load it back to a sheet, but put it in a different sheet. I could have copied and pasted it as well. There's different ways you can do it, but we've now appended some data together. Next, I'm going to show a basic join. There are six joins in power query. In the interest of time, that could be a webinar by itself easily, just to cover the different join types. So what we're going to do now is we're going to start combining our merging data together. I like to call this my lookup on steroids. So let's go Ahead and go in here to our cells data. You're going to click edit. Do that with me. And now we're going to show you how to merge some data together. So we have our cells data. You'll notice we have this skew list data, this store list and a provenance state list. And so we're going to come to cells, we're going to go to merge queries and this time we're not going to click new, we're going to click existing. And so what that's going to do is that's going to bring up the dialog box for merge. Think of merge as a join or a lookup, whatever works for you. So select a table and matching columns to create a merge table. So our first table is the cells table. We want to do a lookup on the sku list so we can bring in the SKU name. So notice here we have it our join kind. 80% of the time you're going to use a left outer join. For every example today we're just going to use a left outer, but there are options. If you're familiar with SQL, you'll recognize some of these. A right outer join, a full outer, an inner, a left, a right and anti. I go into detail in my mo, my modern Excel course what each of those are. You can even do some fuzzy matching options. But for simplicity, here we're going to select skuid and then it says select the same number column here. And notice it says the selection matched 120of120 rows. If they're not the same data type between the two queries, it will not match them at all. Another example where data types are really important. So I'm going to click ok. And notice now it did a merge queries and it brought in a column called skew list, but it's treating it as a table. You'll notice each word says table. If I hover over the table, it shows the sku number and the SKU name. So now I can click right here and tell it what columns I want to bring in. Well, I already have the sku id, so I don't need that. I want the SKU name. I'm trying to add that to my data and I don't want the original column name as a prefect. So I'm going to click off on that. Click ok. And now it performed a new step and I have my SKU name right here. So you could see vegan chocolate, vegan chocolate, et cetera. So I just did that lookup. We're going to do two more. So again go to merge queries. This time we're going to select store list. Come down here. It's the last one. And this time we'll match off store id. Two things to be aware of. You always need it unique. You can match off multiple columns. You could do five or six columns to match second in your your bottom table or your the second table here you want to make sure there's no duplicates. If there's duplicates in that table, you'll get duplicates in your data. That's something you'll learn about joins. That's also an SQL. Your first table can have duplicates. Your table you're joining to should be a basically a lookup or a reference table. And it should have unique lists. It should not have any duplicates in the item you're matching off. So go ahead and click ok. Again you can see the table here we can click on this arrow. And now we could bring in the store name. I'm going to make sure this is clicked off and bring it in. And so now I've added the store store name to this. Let's go ahead and do this one more time just so you really get the hang of it. And this last one we're going to bring in the provenance state list. So I'm going to click that one here. This one we're going to match off the state to the abbreviation. So Again, notice the found 120 of 120 in the real world that may not always be the case. Going to click ok. And here we're going to bring in two. This time I want the state name and the country so I can bring in five or six columns all at once. So we'll bring in two columns. Now I've done my lookups, so that's the merge. Now let's do a future basic transformations and then I'll show the on pivot function. We may run a few minutes over today, probably maybe five minutes. If you need to jump, go ahead. But I want to show these last two things. So now that I have the SKU name here, I'm going to say I don't need the SKU id. So I can come over here and say remove the column. I can also do remove other columns if I want, which means it gets rid of every other column. So I removed this one. Now I'm going to get rid of. I have the state name. So let's just say I don't want the abbreviation. So I'm going to Remove that. I could remove multiple at a time. I'm doing them one at a time, but I could highlight a bunch and I could come here and remove the store id. So now I have the order id, the invoice quantity, the amount, the date, you know, the country. Now let's say I want to sort it in a different order so you can see remove columns. Now I can do. I could just drag this to the order I want. Maybe I want country first. So we'll drag it to the beginning, drop it, and there's country next. Maybe I want the state next. So we'll drag that, we'll put it here and then I want the store name next. Then I want to know what's the SKU we sold and then the date. So we're going to put it in that order. Now we'll start making a few other changes. Let's say I don't want this to say name. I can double click on it and I can change it to state. And you'll notice every single one of these. It's creating a stack every time I do it here. I could have store name here instead of sku name. Maybe I want product here. I could change this to be date and I can go through and change each of these under date. I can also click here and say I don't want date time, I want just date. So we'll go ahead and change it. And now it no longer has the time. And then the last thing I'm going to show is highlight the invoice quantity and the invoice amount. So highlight amount first, quantity second. I can come up here, go to add column, go to standard, and I can divide the amount by the quantity, which will give me the price. And so it's going to call it insert division. And I can just rename this and say price. So that's the price they paid per per product. Maybe that's per case or whatever per palette. So those are some basic transformations. Now I can click close and load and it's going to load it right here with all the changes I made. If I was to append a new file to it, that'd be added. So if we give it a minute here, see? Same, same query, but now it looks totally different and I can refresh that as many times as I want. Last one I want to show real quick is the on pivot feature. So basically on pivoting data is taking it from this, what's pivoted or cross tab data, and converting it to tabular data, which is what you'll want for pivot tables. This is one of my, probably my favorite feature in Power Query. If you dealt with this, you know, if you haven't, you'll probably look at me like, why is this a big deal? All right, so here we have some pivoted data and we're gonna show you how to unpivot it. Here it is on pivoted. So we have it pivoted here, we have it on pivoted here. So what I want everybody to do is we're going to take this data right here. I'm going to have to go to data from table range. We're going to bring it into power query. I want to convert all these dates to rows. I'm going to go to transform and I can just highlight right here. And you'll see there's an option. There's a lot of great options when you right click on the menu bar or not the menu on the, on the shortcut bar here we're going to click on pivot other columns. So what that's going to do is it's going to unpivot everything but the cells rep. And you'll see why I did that in a minute. So go ahead and click on pivot other columns and look that simple. I now have it in the format I want for a pivot table. I'm going to go close and load two and I'm going to load it just below the data right here. So I'm just going to stick it in B11 here. When it's ready, gonna think for a minute and I'm going to say table existing worksheet and B11. Now what I want you to notice is notice I could have renamed these. I didn't. I should have named them date and amount. But that, that's fine for now, just kind of as we're short on time. But notice here, let's say I add 2024 data. So go to Colombo, add 2024. Just copy December. We'll say it's the exact same numbers. Come down here and click refresh and there is January on pivoted for me. That's why I highlighted everything. I didn't want to convert to rows and said on pivot the rest. That way when I add additional dates, it automatically picks them up. Where if I had highlighted just the months and said on pivot these columns, when I added January, it would not have on pivoted it. So that's where why I selected the other columns. Let's do one more quick Example, then I'm going to show you something in Power back in the PowerPoint and we'll let everybody go. So we're going to do the last example here. Let's just do one more example. So you get practice go to data, not. Sorry, not text. That should have been from table range. We're going to bring it in and we're going to come here and we're going to on Pivot product A, B and C, we're going to convert them from columns to rows. So I'm just going to click right here. I'm going to click on Pivot other columns. So now I have it. I could rename these product and I could name this amount. Now I can go ahead and go close and load two and we'll load it. All right, so we're going to load that and then I'm just going to share one thing with you and I'll let everybody go. I know we're right at the top of the hour. All right, so that completes the basics of Power Query. If you have any questions, feel free to throw them in the chat. I'm going to bring back up the PowerPoint here and just want to let everybody know. You know, for anyone who's interested, there's two course offerings I have today. So first is there's the FPA Ultimate Course Bundle. You can click on the QR code there. You can go to my website, the FPA Guy. This bundle includes three modules on business partnering, Financial Modeling, Design, Data Analysis, Driving Value through Smart Analysis, three sections on Modern Excel, Dynamic Arrays, Tables and Power Query. If you sign up this week, it's 30% off the regular price of 130. Just use the code webinar. So that's the first offer I have for you. The second is if you just want the Excel course that I have, you can take the Modern Excel. It's 59.99. You'll get 30% off. So about $20 if you use the code webinar. And there's that. I'd love it. If any of you are interested, feel free to take any of those, those courses and let me know if you have any questions. Bill, I'll come back to you in one minute. But does that compare? That concludes the webinar. I will send out the PowerPoint and the files to everybody who registered later today. So you will get that from me for this webinar. But please let me know if you have any questions in the chat or if there's anything I can answer for you. But that that concludes the webinar. I'll leave you with one last joke, and this one usually gets me in trouble, but that's okay. Does anyone know what the difference is between an accountant and an FPA professional? When an accountant gets creative, they go to jail. When an FPA professional gets creative, they get promoted. All right, thank you, everybody, for joining me. Bill, why don't we do this? Can you. Can you email me? I'll drop my email in here and we could take a look at it real quick. I'll send you an invite. I have about five minutes. We'll see if we can figure it out. So go ahead and just send me your email to that email address. So not everybody on this call has your email. And I'll jump on a call real quick. I have just a couple minutes and we'll see if we can figure out what's going on. All right, on that note, let's go ahead and close the webinar unless anyone has anything they'd like to ask. I'll give it one minute for any questions here, so feel free if you have any. Glad you found it enlightening, Joanna. I hope it helps you with your work. I. I love me some Power Query and Not sure if he's still on, but I'll throw a shout out to Oz who joined us earlier. He has some great courses on LinkedIn, learning for power Query, a lot of great videos on his website. So if you're not following Excel on FHIR, you should be. If you're not following him on LinkedIn, go ahead and do it. His content is gold. You're welcome, Joanna. You're welcome. Everyone who joined, thanks. Gonna go ahead and end the call.