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

Welcome to those who may be out there. We'll get started here in a few minutes. If you're out there, let me know where you're coming from. I'd love to hear. We'll get started here in a few minutes. We're just a little early. Just logged in. William, the Excel file download. I downloaded it myself and it did work. It is a zipped file, so you'll have to be able to unzip it. Let me share my screen and we can take a look. Just a minute. It's wrong one. I want resources Excel files. If I click right here, it downloads the zip and I'm able to open it. Hopefully you're able to see that. So I'm not sure if it wasn't working for you, what the issue would be. I have. I've had no problem opening it myself or others having that issue. Let me move that for a minute. Let me know if anyone else is having that issue or if it was just William there. You're welcome to email me William and I can send you over the file or give me your email, but let me know. Is anyone else having a problem downloading the files? Same problem. Huh? The link in the here, I can just send you the link you should be using. This is the link right there. Try that link and see if it works. Maybe there was an issue that I didn't realize with the link. We'll give it a minute for people to get that downloaded. Sorry for any confusion. I am not sure what happened on my end with that, but we'll give it 12 minutes here and then we'll get started. Larry, download the file. Today. We're going to be talking about Power Query as we get ready to get started. I'd love to know where you're coming from. If you've used Power Query before, any of those type of things, let me know in the chat and we'll start here in just one minute. In the meantime, anyone have a good, good joke? If not, I will tell 1. Got William from the Bay Area in California. Love it. So what did the. What did the constipated mathematician do? So the joke today is what did the constipated mathematician do? Well, he worked it out with a pencil. All right, poor humor. Now we'll move on to Power Query. So this, this should work to download the file. Apologize. The link wasn't working. Hopefully everybody has it. Just a little bit about me. You know, if you've been on this before, you probably know who I am. Paul Barnhurst. Yes, I Agree. Ouch is the right word for that one. I spent most of my career in FPA. I also worked in procurement. I put. I spent 12 years working at companies such as DigiCert, Solara, American Express today. You know, I use the term FPA influencer, but I'm a content creator. I do training, I host multiple podcasts, I do webinars. I share resources to help people be better at FPA and finance. You can see the three podcasts I'd have. I'd love for each of you to subscribe to those on your platform and if you listen to them, please leave a review and a rating. That helps me a lot. You know, there's always a challenge in growing, growing a business and so any reviews or ratings are always appreciated. And that's a little bit about me. Let's jump into Power Query. We have Genghan from the from the uk. Thank you for joining us again. Just as if you're out there, let me know where you're coming from. Would love it. So we did a brief introduction to the FPA guy. We're going to do an introduction to Power Query and then there'll be a couple walkthrough exercises we'll do. There were three files in the zip that you should have received. You're going to make sure you save down all three of those. There's the main Power Query file. The other two we're going to open in Power Query. So let's start real quick with just kind of an overview of what Power Query is. If anyone's used SQL, great. We have Anon from France. Wonderful. We got uk, so we got a few people late at night in Europe. We got the Bay Area. Love it. Always love seeing a global audience. So Power Query is an ETL tool, which is extract data. So you're pulling data from some kind of source, whether that be the Internet, the web, another file, a database, a data warehouse, whatever. Then you're transforming the data in Power Query, then you're loading it somewhere. Power Query is also used in Power bi. It's also used with Azure. Like I said, it's Microsoft's ETL tool. Much like someone using SQL. You can also use SQL and Power Query. So that's basically what it does. And I would say it's one of the most powerful tools in Power Query. Kind of wait, way to think of it here, just a quick little picture. You got a database, you got something in the cloud, you got a file, you got Power Query working with Azure, the Data Verse, Microsoft Excel and Power B And I'm not going to go through this slide. But again, like I said before, this, the whole idea of extract, transform, load, those are the purposes. And there are many benefits to Power Query data integration. Power query has over 100 data connectors. Not all those are available in Excel. And, and all those may not be available in your version of Excel. Different versions have different connectors. We'll talk a little bit more about that. Automation makes you automate processes. Reusability, you can reuse those queries and codes again, again can save you a ton of time. I've seen processes that were taking hours go to minutes with Power Query can be a huge time saver. It can handle large data sets. You can handle millions of records in Excel. With Power query, I've seen 10 million plus. I've worked with millions of records. Now, if you're dealing with millions of records, Excel may not be the best source, but it can handle a lot more than you think. And then it's a low code, no code tool. 80% of what you need to do, you can do all with the GUI interface. Over time you can start to learn some coding, writing some formulas, etc. The more advanced to get, the more you're going to dig into that. But to start using it today, you can use it and do some pretty cool things, all drag and drop. All right, so we'll come back to the rest of this. Now I want to go to the Power Query overview file that each of you should have. So if everybody opens up that file and again, you know, if you're out there, just let me know where you're coming from. If you've used Power Query before or not, just throw it in the chat. Love to see that. And we're going to start with an overview of Power Query. So I have some data here. I went ahead and put it in a table for simplicity. I want everybody to highlight the table with me. I'm going to enable that content anywhere you want. Click in the table, go to data from table range and it's going to open it in Power Query. So what the first thing you're going to notice is it opens it in what's a separate application, but I can no longer work in Excel. Well, this is open. See how I get that hourglass? So even though it's a separate application, it is opening inside of Excel and you cannot work in Excel while it's open. First thing you'll notice is it brings in your data. If each of you did that. Thank you for sharing. Looks like we have Multiple people who have never used a used it, heard of it, but haven't used it. So what it's done is we took our data from a table and I'm going to show that one more time just so everybody can see it. So I'm going to exit out. I have my data, can highlight my whole table if I want our single cell. I'm going to go over to the data ribbon and click from table range and it says, oh, you want to load this January sales data into Power Query. Now what's going to happen here is it's going to give us a menu with some options and I would enlarge it if I could, but unfortunately I'm not able to make it larger. So hopefully you're able to see okay in Power Query here, but what you're going to see is across the top there are 1, 2, 3, 4, 5 menus. First one is file. You have close and load, which we'll do in a minute. You have some options where you can change your settings and you have the help menu where you can research some things or give a response to Microsoft. So close and load is basically the way of saying, hey, I'm done with Power Query. Load my file somewhere. So you've done the extract, the transform, that part is the loading. Next you have this home menu and you'll see there's a bunch of different buttons there. You know, refresh your data, choose a column, remove rows, split columns, group things, append, merge lots of different options to transform the data and you get even more on the transform tab, you know, so you'll see things like group by, rename a header, replace values. On pivot data, you have the opportunity to perform math operations. And if it's a date, you can do different things with dates, like show it by year or month or quarter. You have time operations. So here you're going to see a bunch of different options to transform your data. We will do some operations a minute. But just so you can see one highlight with me column, the month column, which is the second one in our data here. It's not. This is not a grid like Excel in that you see A, B, C, D. It's brought in a table with the column headers and the rows. So highlight the second one called month and then go ahead and go to transform. Come over here to date and just select any operation. I'm going to go ahead and change the date to start of year. You could do start of month, quarter, you know, whatever you want. But I'm just going to say start of year and notice it changed all the dates to 1 1, 20, 24. Also you'll notice on the right hand side here it did an applied step. You can see there's a formula bar right here that shows what that step is where it says table, dot transform, column, source, month, date, start of year, type date, time and it did that calculation. So that is an example of performing an operation. There are the transform ones. The add column has some of the same as transform and a few new ones. The difference is anything you perform on the add column ribbon is going to add a new column versus the prior one where it transforms the existing column. So for example, if I came to date here and I said give me quarter, quarter of year, it's going to create a new column with that quarter of year. Said, hey, I have missed how you have reached to the sheet. Sorry, I just saw that note. So let me walk through one more time that real quick gone. So anyone who didn't see it, I'm going to go ahead and close this. The the way you get the power query sheet is you have to click on data and then come over here to from table range under the get and transform data section. Or you could do it from here, get data from other sources from table range. So you can do it either way. I like to just do this or there's even a shortcut. But that will bring you into here where you can then start to transform data. So we showed add columns and then the last one is the view area. The view area allows you to add information like oh, I want to understand my column distribution. I want to see a column profile. You know, it has this column quality. So you can click on or off how much information you want to see about each column. You could click here and go to certain columns and it will jump to that column in your table. Really helpful if you have like a hundred plus columns, which occasionally happens, you have the advanced editor. If you open that up, that takes you to the code that power query writes. You do something in the GUI interface, it writes code on the back end. The code is called M. It is similar to SQL, but definitely some differences. Like if you're doing SQL, you're going to start with the select statement here, you're going to start with a let statement. And there are other differences, but you know some similarities as well. So this editor, as you do more and more steps, each step you add here gets recorded here and you can see it. So the other thing you have is you have what's called the query dependencies, which that will show what's in your workbook. If you have a workbook with like 20 different queries and all kinds of different sources, this will be really helpful. You could see, oh, hey, this one's coming from the current workbook. There's this table, this over here is coming from the Internet, this one over here and how they're linked. So it's basically giving you a mapping. So Alexi M is very different from DAX in that M is really about transforming and cleaning your data. DAX is more about performing calculations with your data. There are things you can do that are very similar with both of them. So you know, if I'm preparing something for Power BI or I'm preparing something for a data model to write DAX to display it, I, you know, I could do some of the work in Power Query and I could also do some of the work in dax. My general view is I try to do as much of the transformations as I can in Power Query and then once I have the data in a format that I think is a good data model at the right level, I will do the rest of it. Writing the actual calculations in dax. So there's a little bit of learning to it and you know when to use which. But the way to think about it is DAX is for your calculations at the end of the process when you finish your data model. Power Query is for bringing all the sources together and for transforming it so it's in the shape you want for your data model. And the way to think about dax, I like to joke, is DAX is basically if SQL and Excel had a baby, that would be dax. So there we go. All right, so back to this. Here we have the query dependencies, we also have the query settings. I'm going to bring that up for a minute and you can see the query settings is this pane right here. You can either have it on or off. So if I delete it, I have to come over here to query settings. The other thing is you have your options and settings. If I go to Query Options, I can review different options for my data. Like, hey, do I want to show a sneak peek of my data? How do I want to load it? Do I want to display the formula bar or not? So just a number of options for your settings, regional settings and different things. We won't talk much about that but, but that's available there. So that's what I wanted to start with is to give a just a very high level overview of the interface. I'm going to Pause here before we move on to some other things and just see if there's any questions so far. So if anyone has questions, feel free to ask them now. All right, we'll give it a moment. Not seeing anything come through. I'm going to keep going. The one other thing I want you to realize here that's very different in Power Query from Excel is notice there's a data type. See where it says ABC123? ABC123. Yours may not say that in my settings. My settings are set up so that it doesn't try to assume the data type. And that's in the query option. I go back here when something's loaded to Power Query, it can try to assume the data types right here. Type detection always detect column types and headers for unstructured sources. Detect column types and headers for unstructured sources are never detect. I say never detect and I do that so I can select what data source I want. There's not a right or wrong way to do it. When you're first starting, you're probably fine to just let it automatically detect. And that's probably the default settings. So you probably don't have that ABC 1, 2, 3 here on each of these like I do, which means if I click on them. So if we go next to products and click on the abc, you're going to see a bunch of options for the different data types. You have a decimal number, a currency, a whole number, binary tech, you know, date, time. I'm going to make this one text the second one here I want to be date. I don't really need the time so I'm going to shorten it. So it's going to give me date only. This third one here is a whole number because it's quantity and then the price. I'll make currency. And notice in the FX it showed each step it says transform column types, sources, products and hey said make products text, make month, date make quantity and integer of 64 make price currency. So you can start to see the logic it uses to write this. And you could go in and edit any of these. Like I could edit this from date to text if I wanted. And so that's usually the first error, not text after delimiter I want just tax it will give you an auto fill and sometimes notice it gave me an error. And that's because I put a capital when I shouldn't have. Everything in here is case sensitive. So let me see if I can rewrite this. I really can spell most days all right, there we go. And so now this is text. I could change it back to date. I could always delete this step if I wanted to go back to where it was. I can look at different steps, I can go to Properties and I can write a description. So, pulled data in from Excel table, I can tell people what I did. I can rename the step if I want, and it will always say that, save that. So let's say you're making changes to that table every week or every month or every day. You can come in and refresh it and the table will update. Those steps will always be there. So in that way, it's kind of like a macro. All right, so I'm not going to save this table. So I'm going to go ahead and just. Actually, yeah, no, we'll go ahead and say discard. We'll show you the next thing in a minute. So that's an overview of the interface. Now, on the next tab, I've given you a list of the different types of data. So you can see text, whole number, decimal number, currency, binary, true, false, etc. Those are all the data types that are available in Power Query. And it is important you get the data types right. You can't perform a math operation with a number and text, even though they look the same or you know, something that looks like a date but is not a date. So it is important that you select the right type of data type for. For. For your data. Next, I put a list of all the Power Query connectors that are available for Excel. The list is different for Power BI or Azure. There's even more. So you can click here and see. If you go to this website, it's going to show you all the connectors and they update it from time to time. So you can see, you know, access database and it tells you how to load things and it gives you advice. You can see here each of them in which tools they work in. Oh, yeah, it works in Power Apps, works in for Excel. And so you'll be able to see all these different. Very long list of data types, you know, depending on where you're in. LinkedIn is available for some things, but not Excel. So there's a lot of different connectors available to different tools. I've given a list of all the ones for Excel here. All right, so that's a little bit about connectors. Next, we're going to practice. Give me one second here. I'm having a computer issue. Sorry about that. Just had a small computer issue. I'm back now. So now what we're going to do is we're going to practice loading data and we're going to load it first and then we're going to start working on it and making changes. So you can see how Power Query works. So I have this file. Here's my January sales data, here's my February sales data. I'm going to load these to Power Query. So I'm going to go data from table range, bring it in. I'm not going to make any changes right now. We'll make all the changes later. So I'm going to come over here to close and load. Click on it and I want you to select Close and Load two. That's going to bring up the import data box where there's going to be four options here. You could load it to a table, you can load it to a pivot table report, you can load it to a pivot chart, or you can only create a connection here. What I want you to do is select only create connection. Click ok and I'm going to switch my screens here for one minute, so just bear with me. The screen's a little bigger, so I'm hoping I can enlarge some things and make it a little easier to work with. So I've loaded that first file. Notice on the right hand side there's this Queries and Connections pane that shows that January data says it's Connections only. If that's not showing up, come up here under data, click on Queries and connections and it will show. So we're, we're about 25 minutes in, so I'm going to pause for a minute, see if there's any questions and then we're going to load several other files in preparation for an exercise. So any questions from anybody so far on Power Query and what we've done. Anything you need me to redo or any questions, let me know. All right? No questions. I'll assume everything's going good for people. So we've loaded January, we can see it there. We're going to do the exact. Yeah, no problem, William. We'll redo that one time here real quick. So I'm going to right click here, I'm going to delete it and we'll just redo it. I'm going to come to January, click anywhere in the data, come over to the data ribbon under git and transform, I'm going to select from table range, it's going to open it up. From there I'm going to select file, close and load two. I'm going to only create a connection so it just stays in power query. And I can see under my queries January data connection only. Now I'm going to do the exact same thing for February. So this will be a little boring, but I'm just going to load it. I'm going to do close and load two only create a connection for the moment. Notice this one, it called it Table 3. The first one it called January. It's going to use whatever name I've named the table. So ideally you want to name all your tables ahead of time. I just wanted to show you an example. I think it even says April sales data. This really should be February, but you get the basic idea. All right, so we've loaded that. Now we're also going to load all three of these lookup tables. We will use them a little later. This is where you'll start to see the power of power query. Pun intended. All right, so I'm going to come here, I'm going to go to data from table range, close and load two only create connection. All right, so I've done the skew list. Now we'll do this one from table range. That's my store list. Close and load two only create connection state name table from table range, close and load to only create connection. And now I've loaded two months of sales data and some tables that I can use to combine my data together. We'll see that a little later. Before we get get into the append and merging of data, I want to show a few other load options. So first I'm going to take a minute and just show you what you have available. You'll notice if anyone looked at the data, you'll notice there's from table range. You can know you can load a table. You can also load a named range. If you give your data a named range, you can load it that way into power query. You have you can do things from a picture, you can load something from the web. So if there's a table out on ESPN or Wikipedia or an economics website, whatever it might be, you can go get that data you have from text or CSV. And then you have several other options here. What options you have available will vary depending on whether you have a personal account, a business account, or an enterprise license, which is an E5. Each of those have different options. So if yours looks a little different from mine, that is okay, everybody should have this one. The from file, you can load something from an Excel workbook from a text file. From an xml, from a JSON, from a PDF, from a folder, bringing in multiple files from a folder. You can load things from a database, from Microsoft Azure, from the Power platform, so Power bi from the Data verse. And there's even other sources here, an OLEDB connection, an ODBC and OData feed. You know, all of those are options that you can bring data in and you can bring data from multiple sources into the same file. And we'll do that now. So what we're going to do now is we're going to load the CSV file. It was called January cells and we're going to load and place it right here in cell B9. So right now I'm going to say load from text, slash CSV and I'm going to find where I saved that file. Wherever you saved it on your computer, go to that location. I'm going to go to Excel Hour here, Power Query. And you can see right there I have January cells available. I'm going to click on January Cells. I'm going to click Import. It's going to think for a minute. It's going to bring up a dialog box similar to this and it's going to have three things. It's going to assume what the file origin is, what kind of code it is, 99 of the time it's right. If not, you have some kind of special code you can go through and select it. You'll see there hundreds of options here. Of the different ones you can select next delimiter. Generally it's going to assume default. Most things are going to be comma separated, but you can even use custom separated or fixed width or spaces or semicolons. There are times you have data where a comma is being used to maybe separate. If you have city and state in the same column and so it throws your data off. So sometimes you'll use other delimiters. Then it tries to detect the data type and you can base it on the first 200 rows. The entire data center can say do not detect data types. I'll go ahead and leave it as the first 200 rows in this example. And then we have two options here. We could load the data where it says load or load to, or we can transform. I always select trans transform to bring the data into power query. So if I want to make any changes here, I do want to make a change. So we've loaded this from a CSV. All of you are going to notice one thing. The column headers are in row one. I don't want that. I want my column headers to be up here so I can click right here on this little drop down table thing and say use first row as headers because it knows that's a common issue. So now I have that. I could change any data types I want. Like maybe I want to change this one invoice quantity to be a whole number. This one I want to be currency. This one I want to be date. These IDs I'm going to leave as just numbers are just text because I'm okay with that. They don't need to necessarily be numbers. They're not numbers in the traditional way we think about it, they're identifiers. So now I'm going to close and load and in this case I'm going to click just close. And I'm actually going to do close and load too. So I can select the location. If I choose close and load, it will load it to a new workbook, our new worksheet. So sorry, not workbook but a new tab or sheet. So I want to close and load two because I want to select the location, I'm going to say table, existing worksheet. And B9, you can see B9 is selected. If I wanted C, I could put it there, you know, wherever we'll go. C9. At this point I'm not going to load anything to the data model, but that's loading it to Power pivot, where you could then perform DAX and do other things. We won't get into that obviously in the time we have here, but if everybody's with me, click OK and I've now loaded a file. Notice there are 60 rows loaded. If I was to go back to the CSV file and add 10 new rows and then click refresh and in fact I'll even show you that we're going to go to where that CSV is saved. So I have it here. And what I'm going to do, just so you can kind of see it, is I'm going to take all these records and I'm just going to duplicate them just for this exercise. So I could delete those later if I wanted. But now I have double the records, right? You can all see I added those at the bottom. Click yes, come in here. And then what you're going to do is you're going to go to data and you could say refresh all or just refresh. To refresh just this table I'm going to do just this table. I'm going to click refresh. And now Notice I am going to have double the records I'd only wrote. Oh, you know what I loaded? Sorry, this is the table I loaded in. I accidentally did not update the right table. So you can see now we're going to do this again. So I'm going to come down here, I'm going to close this. I'll hit refresh this time I'm just going to right click and do a refresh. And you'll notice I now have 120 records. Everything's in there twice. So that's the beauty of Power query is when files change, all you got to do is hit refresh and you'll get the new data. So that's loading something from a CSV. Next I want to load from a PDF and I've given you the steps of how you load something from a PDF so you can go back and look and see it here. January cells should be there. When I look at the pivot table power query files, it should be full name January cells. So if you're not seeing it, I'm not sure. Is anyone else having that problem or did everybody. Is everybody getting the January sales file? Let me know if you could just comment. Okay, I'm not, I'm not seeing anyone else saying it's an issue. So I'm not sure what's going on OPU there, but hopefully you're able to get that. But you get the basic idea that's the only time we're going to use it. So you know, if you're never able to get it, feel free to email me and I can send it over to you. I'm going to keep going since I haven't heard anyone else saying they have that problem. Next, we're going to load data from a PDF. If you want to see the instructions, there's some instructions here. We're going to come over here this time we're going to go to data, get data from file from PDF that's going to ask me where it's stored. It brought up my path. I'm going to say it's right here, sales rep data. I'm going to hit import. It's going to make a connection, going to think for a minute and it's going to bring up this navigator box and it's going to find all the images that it's pulled out and converted and usually you want it. Ideally, if the PDF is in a table, that's going to work best. You'll notice here I selected the table, it shows all the data. I could also select this page one and sometimes it will show the same, sometimes it'll be a little different. How good the the PDF works will vary. This example is a really good one. You know, set up to show that in some cases, if the data, especially if it was ever saved from Excel or was saved in a good table format, it's quite easy to bring in Power Query. If it's a really messy PDF, probably not going to come in very good, but you can generally clean it up. So we're going to go ahead and click Transform Data and it's going to again, once again bring us into Power Query. We're going to. We need to clean up our column headers again. So I'm going to click here, use first row as headers and then I'm going to look and see do I make any changes. I want my sales target to be currency, I want my sales amount to be currency. I'm good. I'm going to go close and load two and I'm going to say table existing worksheet and I want it here in B8. Click okay and you can see it brought in my table. Now you'll notice the formatting does not come through from Power Query. The data type in Power Query is, is there for Power Query and is important for the data model. But when you bring it into Excel, it's just going to treat both of these like a number and you can apply whatever formatting you want to them. So I can right click and you know, go to Format cells or hit Control one or, you know, whatever way you want to do it. There are different ways. I'm going to come in here, say I want it to look like this and now I have some formatting. So what we've covered so far, we got about 15, 20 minutes left. We've covered the basics of the Power Query interface. We've talked about connectors and data types and we've talked about loading data. So when we talked about that extract, transform, load, what we're really doing here is we've done a bunch of extracting data and we've loaded some data. Now we're going to work on appending data, which is incredibly powerful. Appending, joining, merging, really kind of cleaning up the data. And we're going to do a couple simple exercises. So the next thing we're going to do here now is if you remember, we saved January in Table 3 into memory. We only created a connection. We can go back to those at any time. So I'm now going to come over here. Click, click on January data and say edit. So you can see I see all seven of my queries now. And the nice thing is I can even group these queries if I want. So if I wanted a new group that said sales data, I could do that. I could put a description and it's going to give me a folder and I can move things into that folder. So I could say I want January and Table 3 under Sales Data, and so you can group things together to keep order. Now what I want to do is I want to combine the two of these. So the first thing I have to do before I combine them together, so I'm going to put them all in one table is I need to make sure they're the same. So like order ID, store ID, there's 1, 2, 3, 4, 5, 6, 7 columns. 1, 2, 3, 4,. 5, 6, 7 columns. They all have the same data type. They all have the same name. I'm going to change one data type. I'm going to change this to text and see what happens when I try to bring these together. So I'm going to click on January data. I'm going to go to append queries, and in this case I want you to click on. You have two options. You can append the query in the existing query, which means in the January data, I want to bring in February or Table 3, or you can create it as a whole new query. We'll go ahead and create a new query. So I'm going to do that. It's going to say do I want. Do I have two tables or three or more? I generally will click three or more because I like the interface better. But I really only have two data sets. So now I can come down here and select what I want to add. I can change the order. Do I want February 1st or January 1st? Doesn't really matter. I got January data, I got table three. I'm going to click ok. And you'll notice it did combine them together. And it did. It was okay. It was smart enough to say, even though this says text, I'm going to make this 1, 2, 3. There are other situations where it won't work and if you have different names. So for example, if I change this to order instead of order id, then I come over here, notice now it gives me an order column and at the end it gave me an order ID column because like, hey, they're different and each of them are null for half the data. So when you're appending stuff together, you just need to make sure your Columns are named the same and it's case sensitive, so that's going to be different. This D has to be capitalized. Now, once I do that, come back here. I've appended my data. I can come over here and rename it and I can say all cells data. And now we're going to load it. So I'm going to come to close and load. Close and load two. We're going to put it in this append file. So I'm going to say existing worksheet. I want it in B8. You can see a pen files, B8. And now I have both months of data. If February was added to it, I could refresh it again. You'll notice the invoice dates didn't come through as a date. I could always fix that and change it in Excel. All right, so now what we're going to do is we're going to come back to this all sales data here and we're going to do a little bit more work with it. So I'm going to right click here on all cells data. I'm going to click edit. I'm going to bring it back up. The first thing I'm going to do is I'm going to clean up all of my data types. Yours may already have been assigned, so if you're okay with them, that's fine. But I'm going to change this to text. I'm going to change this to text. I'm going to change this to text. I'm going to change that to text. I'm going to change that to, to a whole number, currency and date. All right, so my date is cleaned up. Now what I want to do is you're going to notice here that I don't have the store name, I don't have the SKU name, I don't have the state name. I want to add all those things. That's why we brought in these other tables. The skew list, the store list, the state list. We brought these in so that we could clean things up. So now we're going to go to each of them and we're just going to quickly fix the data type. So here, I want all three of these to be text. Here again, I want text for both of them. And this one as well, I want text. The reason we're doing that is so they'll be the same when we join them together. So now I will show you how we can basically do a lookup. So here's our data. I want to look the store ID against this store list and return the store name. I want to look up the SKU ID and return the SKU name and I want to look up the abbreviation for the state and return the name and country. We're going to do all of that. So power queries merge or join future feature is kind of like look up or xlookup on steroids is the way I think about it. So now I'm going to do merge queries, not merges new but just merge. It's going to come up and say, hey, what table do you want to merge with? So the first one is my original table. Second one is the one I'm merging with. I want to merge it with skew list. So now I need to tell it what columns to match on. So that's like how do I join it? Store ID to store ID. Alrighty, so it only matches 20 of the 120 rows because I only have 5. Oh, because I did this wrong accidentally did store ID to SKU ID. I want SKU ID to SKU ID. So now I'm matching all 120 of 120 rows. So what you're going to want is SKU ID here, SKU number here. You click okay and it's going to bring in that table. So this is kind of like the join. And if I look here, I can see down at the bottom when I click on the table. Oh, it brought in the SKU name, SKU number and SKU name. I have the ability here to click now and to tell it what columns I want to display. Well, I already got the SKU name over here or this, not skew name, the SKU id. So I'm going to skip the number. I'm going to select just the name. I'm not going to use the original column name as a prefix. I want just skew name. Click. Okay, I've now added the SKU name. I can come over here now and delete this. And nothing changes in the underlying data. It just changes it in this view. Because I now have the SKU name, I don't need the SKU id. So I've done that. Now I can do it again. I'm going to do merge query next we're going to bring in the store list and so I'm going to match store ID to store id. I'm going to click ok, it's going to bring up a table. I'm going to do the same thing. I'm going to click here, select just the store name column. Make sure this is turned off. And now I have the SKU name and the store name. So I can go delete. Go ahead and delete my store id. Next. I want to bring in the state information. So I'm going to do a third merge query. And so I'm going to click merge queries here. And I'm going to go down to my state list. And this one I'm going to match on state providence to abbreviation. Click okay again, come to this double arrow here. Select what columns I want. I don't need the abbreviation. I do want the name and country. I don't want the column name. Click okay, I now have that. I can get rid of this over here. And there's my data. I've now just appended two months together, merged the data with other queries. So I started that cleanup process from there. I could do other things like maybe if I don't care about the order id, I could come in and remove that. And then I could say, hey, I want to group my data together. So I'm going to do a group by and I want to group it by store name, skew name, and name, which is the state name. I could rename that. And then how do I want to sum it? Well, I want to sum the invoice amount. So I'll call this amount and you can add other aggregations. You have multiple sums you can do. So we're combining the day together very similar to a group by in Excel. I'm going to sum my quantity. I'm going to call this quantity. So what I did is I'm grouping it by sku name, store name, name. I'm ignoring country, I'm ignoring invoice state and saying I don't care when it was. I just want to group it all together. I click okay, it now performed a group by and summarized my data. I can go ahead, go file, close and load. And it's going to load that data. And it, it ended up loading it here because I was already in that file. You can see it refreshed that and gave me a whole different kind of setup of data. So I want to pause here. I just have one more thing I want to cover, but I just want any questions from anybody foreign not seeing any questions. So I'm gonna assume we're good. The last thing I want to show real quick is how you unpivot data. Often we get data that's pivoted. And what I mean by pivoted is it's cross tab, not tabular. So the months are in the columns instead of in rows. This is more normalized or database data. Right. Each row is a record and you could see sales rep month 1, sales rep month 2, sales rep month 3. Where here we have sales rep 1, 2, 3. This is often report layout or summarize data. There are many times when we want to convert this to this, which isn't very easy in Excel. There are some better ways to do it now with VStack and some other functions. But this is something that is super easy to do in Power Query. It is one of my very favorite features. So if I want to convert this to this, which you'll see I have some pivoted data right here. If I wanted to convert that to look like this, we're going to do that together. So I'm going to take this data and I'm going to say from table range and load it to Power Query. So I have it in Power Query. You know, I could go through and fix all the data types. I'm not going to for this exercise. If I was really using this data I would. But you know, I could highlight all these at once and do. It makes it a little easier. But what we're going to do is we're just going to right click on sales rep. So. So everybody that's with me, right click on the sales rep and you're going to have two options. Unpivot columns, unpivot other columns. We're going to select other columns. And notice what it did. It converted everything from cross tab to tabular. So now I have three columns. I have my sales rep. I'm going to make that text. I have my dates, which used to be my columns and I have the amount. So now we'll just call this currency. So now I have the data in the format I want for a pivot table or other things, much more useful. It's now converted it so now I can close and load it. I'll go close and load two. I'm going to put it on. You'll go existing worksheet. We'll put it here and I'll just stick it next to this one here and you can see. There you go. I haven't renamed it like this Other one had been changed. Attribute should be named to month or date or whatever you want it to be and value. But basically that is how you unpivot something. There's one other example here if you guys want to practice now notice when I did this, if you notice I did on pivot other columns. So what it did is I only highlighted sales rep and an on pivoted everything. I do that. So if I ever add a column, so if you ever add additional data, it will automatically unpivot it. So let's just take these. We're going to add February. It's going to be the exact same numbers as January. Come over here. Hit refresh. Come down to the bottom. Right there you can see 2, 1, 20, 24. It added that to all the months. So if you have a report, it's in report format, you need to convert it to a pivot format. You're going to be adding new months all the time. You can use on pivot, unpivot other columns. And with just a couple steps in one or two minutes, you now have the report in the format you need. All right, so that, that covers the majority of the training. I want to go back to the Excel file, cover one other thing here, and then we will open it up to any questions. All right, so we talked about Power Query, how you're extracting. We listed all the advantages. And so what I just want to share is, you know, I try to do these type of trainings about twice a month where they're free. So sometimes I cover subjects I've covered before, sometimes they're new subjects. I'm always open to ideas of what people want me to cover. I also sell training, obviously, you know, I'm trying to run a business, and so I just want to share. For those who may be interested, if you're interested in learning more about modern Excel, I have a course. It's the same one that's used in the Wharton Online Wall Street Prep FPA course. Much of their Excel stuff comes from this material here. There are three modules, about five hours of video, probably, you know, seven, eight hours of training, an overview of Excel, tables of Power Query that goes quite a bit more in depth than we did today. You're going to get another hour of video. And so you probably got three, four hours of training. And it talks about dynamic arrays, which I consider kind of three of the cornerstones of modern Excel. That course is normally $60. With the code webinar, you'll get 30% off on that. So that's an offer. You'll see the QR code there. And I will send that out in an email, send this out to people for anyone who's interested. So I'd love, if you're interested, to register for that. That'd be great. And then there's also what I call the ultimate course bundle that has all the Excel stuff, but also has some FPNA material, some business partnering, some modeling design, and some data analysis. You can see the code there. Would love for you to take either of those courses and that pretty much wraps things up. I will stay on for a few minutes in case anyone has questions. We got about three more minutes before the top of the hour, so please feel free to ask any questions you want of anything we covered today. Any suggestions on what I should cover in the future or just any general questions. So I'll give it a minute here to see if anybody has any questions. Alexa so the example reason you would on Pivot a table is a pivoted table is in a report view and on pivoted table is going to needed for a data model. You want it normalized. If you want to load it to a pivot table then you can have the amount as one column instead of having to bring in every single month. So those are two examples that you can do Alexa, that I think are really important for on Pivot I use it all the time to take any kind of report, any type of data that I want to see it in a normalized, more of a data structure because I want to use it for a data model or I want to put it into a pivot table. I will always on pivot at first. So those are two examples. Hopefully that's helpful. All right, we have one other question up you which course would you recommend for someone who's new to modeling, finance, accounting? I mean. Well, that's a good question. Those are usually more than one course if you want to learn modeling, finance and accounting. I do have a good course. The design principles I think is good. If you're new to Excel, I think that's really good. I think my, you know, my FPA ultimate course bundle would be good. It's not going to cover accounting though. I mean Wall Street Prep has a great basic accounting course. If you're looking for really good modeling courses, anything by Chris Riley. I also highly recommend the Financial Modeling Institute and doing their AFM program where you get their fundamentals. It's the same modeling training that's provided to everybody who does the cpa. You get an exam that you get to take an exam that says look, I'm qualified. I can build a model to show your employer. That program runs about $600 between 6 and $700 with a discount code called podcast that will give you a 15 off. So I'm a huge fan of Financial Modeling Institute. Like I said, anything by Chris Riley on The modeling side, you know, if you're looking for fpa, I also have several in person courses. You can find them on my website. Also offer corporate training, some other great people in the FP and a space that I really trust as far as training goes. Carl Seidman is really good. Christian with TIG is really good. So there's a lot of options out there. I'm a little biased toward my material, but at the end of the day, as I always tell everybody, it's finding what's right for you. So hopefully that helps a little bit with that and any other questions from anyone, I'll just leave it open here for a minute. Yes, it was recorded. Craig, what happened on the hour, I think with daylight savings. We set this up a few hours a few days ago is one. It was showing 2 o' clock on my computer when I set it up, but I think Streamyard showed three. I sent the email out today to let everybody know it was two. So there was some confusion there. Craig, really sorry about that. There is. You can watch it on demand here in Streamyard. You will also have it available on YouTube. It leaves it up on LinkedIn for a while. So, you know, go ahead and watch it, ask me any questions. I do know we had a few people join right as we were getting ready to start. And so, you know, like I said to Craig, really sorry if there's some time confusion. I'll make sure to check that next time. I think with the daylight saving, somehow my computer and Stream Yard did not update the same and it got an hour off. So I really apologize to anyone who just joined. But I'll stay on for, you know, another couple minutes. You're welcome to drop. I don't have anything more to cover. Just answer any questions anyone has. You can. It could be any question about Excel, something we covered, whatever it may be. I'll hang on for a few more minutes for any questions. But thank you everybody who joined. And again, apologize if you're just joining now. We did start at 2. I'd love to redo this, but I do have to pick up my daughter from school so I have to leave here in a few minutes. But I'll hang on for any questions. And while we're waiting for questions, I'll tell a joke. So what's the difference 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. That's why I work in fp. And a thank you for the kind words Alexi, I'm glad you found that really helpful. Appreciate it. Alrighty, so I'll be on for another minute or two if there's any questions from anybody and if anyone has suggestions on what they'd like me to cover in a future session, please let me know. Torn between which career path to choose. Yeah, I can always be tough. If, if you ever need a sounding board, let me know. I mean, there's pros and cons. Whatever way you go as far as career paths, it's all. It's all about finding what we want to do. And at the end of the day, that may change over time. So the reality is most of us switch careers more than once. I'm. What I'm doing today is not what I was doing five years ago and was not what I started. My first career is procurement. My circuit was finance and fpa. My career today is training and content creation. So the good news is it's not irreversible no matter what you choose. All right, I'll hang on for two more minutes. We'll go till five after. So under Andres, I don't. I do not forward. I'll try to forward the recording to everybody who registered, but typically it is available right here where you're registered. You can come back and watch it anytime. It'll also be on YouTube. But yeah, I'll try to send out a link along with the PowerPoint from the training today. You should see something in the next day or two. So, yeah, I'll go ahead and do that. All right, one more minute for any last minute questions from anybody. You're welcome. Foreign. I think that that about wraps things up. Thank you so much for carving out some of your time. It's always a pleasure to have people join me and everybody. Have a great evening, morning day, wherever you may be. Appreciate you joining the FPNA guy here today. Thank you.