Excel Power Query: Clean & Transform Data Like A Pro

1. Introduction to Power Query: Welcome to the Excel Power Query crash course. Your shortcuts to faster, cleaner, smarter data in Exl. Have you ever wasted hours manually cleaning messy data, deleting duplicates, or fixing endless formatting issues? What if you could automate all of that in just a few clicks and never touch the messy data again? This is where Power Query comes in. Power Query is one of the most powerful yet often overlooked tools inside Excel. With Power Query, you can clean messy data instantly, merge multiple data sources together, automate repetitive tasks, and completely transform raw unorganized data into clean analysis ready reports without writing a single formula. I helped students unlock Exlsedin features to become faster, smarter, and far more efficient at work. In this hands on crash course, you will learn how to load data from multiple data sources, how to clean and transform data with just a few clicks, how to combine tables without complicated formulas, and how to build fully automated refreshable reports in Exle whether you're business analyst, freelancer, accountants, or office professional, someone preparing for data every job roles, or simply tired. Of wasting time on repetitive data cleanup. This course is for you, don't worry. You don't need any power query experience to get started. We will go step by step with real world examples, practical lessons, and downloadable data files so you can follow along. Gain a skill that can save you dozens, even hundreds of hours throughout your career. Don't wait. Let's get started transforming your Excel skills with Power Query today. I will see you in the next lecture video. 2. What is Power Query?: Hello, guys. In this lecture video, I'll be teaching you what is Power query. Yes, Power query is a built in tool in Excel that lets you input, clean, transform, and combine data from multiple sources without coding. Yes, there is something called ETL. E stands for extracts, T stands for transform and L stands for load. So you extract data from the primary or secondary sources, which can include an El workbook from external databases, from tables arranged from pictures from PDFs, from XML, from folders and the likes, you extract it from there, then input it into Power Query. Then Power Query helps you clean and transform the data perfectly well, which are going to learn how to do that in this course, and then combine that is when you combine datas, you might have datas from multiple sources, actually. You might decide to import data from one source, and that's fine, okay? You might decide input multiple datas from the same or different sources, you have to combine them and transform them. When you're done with that, you load them back into El or Power BI, and it is without coding, you don't need to code anything, you don't need to know how to code. It's very simple. I records each transformation step automatically using M language. Language is not machine language. It is a Power Query formula language used in Power Query. You don't know any formula. It records everything for you. The different steps you make when you delete something, you add a colon, you add a rule, you add a table, you replace values, you format text, you trim, you clean, you subtract, you multiply, use the dates, and every other options we have in Power Query, it records everything so that it can automate repetitive task for you. In Power Query, which I'm going to teach you how to do that in this course. Also, Power Query allows you to refresh and repeat the process anytime, yes. I'm going to teach you how to do that in this course whereby we're going to clean and transform the January to May sales reports perfectly well in Power Query. Then after that, we're going to add the May sales reports into the folder where we have the January to April sales report. Without importing into Power Query again, Power Query would automate the extracting, transforming, cleaning, and loading processes, which we've done the January to April sales reports, I will repeat itself automatically within seconds for the May sales report. You can keep on adding the June, July, August, September till the end sales reports. No matter how much it is, it will do that automatically itself. I'll be teaching you all these in this power query course. I will see you in the next lecture video. 3. How to access the Power Query Editor in Excel: Lecture video, I'll be teaching you how to access the Power Query editor in Exle. Yes, we need to know where it is. We need to know how to access the editor in Exle. It is very, very simple. This is the O Okay, just come over to data. It's very simple. We're going to learn more about this because this is where exactly would be able to access the different types of data sources, the primary source and the second resource, which are going to learn in this course, we have different ones. But for us to be able to access the Power Query editor in Exl, just come over here to launch Power Query Editor. We can launch the Power Query editor in many ways, We can actually launch it through the different data sources, but we will learn that in this course because we need our resource file before we can be able to do this. Let's launch an empty Power Query Editor. Okay, it's loading, so just a little time. Okay, it's loading. So you can see. So now you can see it is empty because I didn't open it with any data, so this is how exactly it is, we're going to learn how to open it with data and also how to clean and transform this data and also make it automatic. That is exactly how to open a Power Query editor without data. The best way is to open it with data, which you're going to learn in the next section. This is how the Power Query editor looks like. We have the different menu tab, the OM, the transform, the At column and view. Most of the important options are grade out because we don't have any data yet. To go back to Exil itself, we can actually close this or minimize this. When we minimize this, you would see there is no way you can actually click here. It's not possible. They are pached together. The best thing is for us to close this because we've not cleaned any data or transformed any data here. If we've done that, we just have to close and load here, but we've not done that, so we need to close it and go back to what Exit. In the next section, I'll be teaching you how to import data from multiple sources into the Power Query editor. That is where we start to clean and transform the datas we have to make it clean for different analysis. I will see you in the next lecture video. 4. Download the Resource Files: Guys, this is the resource file. You should download the resource file in the lecture after this lecture from the link in the lecture. Just double click. Firstly, we have the diagram itself, the diagram are used in this course, then we have the resource file. Let's look into the resource files. We have the resource file itself, which we would be cleaning and transforming in the power query and loading it back into EO. Then we have the gender and department table, which are going to merge this, combine this with the main sales report. So after that, we are going to what we're going to add the May sales report. So they already finished transformed and cleaned data. So the mail sales report is also dirty. But after using Power Query, when I input the mail sales report into the January to APR sales reports folder and refreshing Excel, it will automatically clean and transform the mail sales report, then add it to the what to the January April sales report, and we have it automatically done. So it's very, very simple. So I will see you in the next lecture video. 5. Primary Data Sources: In this lecture video, I'll be teaching you how to import data from the primary source. Just come over here to data, which is a normal place where we find the datas to be able to input them into Power Query. Come over here. We have all this. You might not understand this icon. Look at this put your cole head. We are from text and CSV, we are from picture, from web, okay? Recent sources recent sources you've used, it's going to show here or you click on it from table, orange, and lastly, from common sources. Everything we have here, you would actually find it here when you click here except from this. But this is very simple. Import data from common sources, just like the common sources we have here, the primary and secondary. When you click here, we have actually much more options for us to choose from. When you come over here, the main primary source for our power query is from the Excel workbook itself. Probably we have the Excel workbook already here. The worksheet is here already, we actually doing some things. We have it. I do not want to open this number to open it, just for us to see exactly what's happening. Exactly, this is the main primary resource. Also when you come to from other sources from table and range. In our resource file, we have the table, the sales report table. When we click here, it is the same thing as coming over your clicking here. You can see from table to range. So going out is perfectly well. So when you have your data as here, just click here from table to range from table or range, exactly this is the primary source itself. This is from table range or also from Excel workbook. Definitely, you might have the Excel workbook in your documents on your system. So it's a primary source. Just like we are going to open the Excel workbook from our resource file. Apart from that from text, or CSV. When we say CSB files, they are called text files. There are also datas we can actually use in Power Query. We can actually clean and transform them and make them clean datas. CSB files, textfils they're just like text, but we can actually make them into tables and clean them perfectly well. Sometimes when you open CSV or TextFile, it would actually come as table when you open it in Exle. XML and JCNFle are second resources. Let's go to what let's go to PDF. PDF is also a primary source when you have a PDF on your system already, except you're going outside probably to a website to download, that will be second resource. Of a primary source, PDF whenever you have it on your documentary system, it is a primary resource. The PDF might have table in it, the table exactly is what we want to extract and transform. Apart from that from folder, we are going to be using folder also in this course. At the end, we're going to add the May report to the January, February, March, April report, okay? They will need to add the main report to it and it's going to automatically clean the main report, just like it has cleaned the January to the April report. These are the main primary data sources. I'll see you in the secondary data sources. 6. Secondary Data Sources: In this lecture video, talking about the second resources, there are sources outside of L sources outside your document, sources outside of your let me say your system, actually. Now the second resource XML, starting from XML, XML files are structured files, often exported from other systems, we don't usually use it. J in file, this is a JavaScript object notation format. Often from APIs or web exports. You can actually get them from web JavaScript deals with web. Apart from that, we have from database. We have databases all over the world. We have Microsoft Azore, we have AWS, we have Google Cloud, and some other ones. Just like databases, the way the data is being stored, a particular company might actually have the data stored in the cloud. That is what most people are doing now. Most companies are doing now. They have them in the Cloud. You have to extract that data from the Cloud. Actually, Excel enables us to be able to do that from database, SQ server, the Microsoft SQL Server, Microsoft Access database, Analysis Service. SQb server analysis services database. Also from Azure, as I've said earlier, then fabric, this is new to Excel. Microsoft created fabric, also just like a database for us to use, just like Azure, actually, but this is the latest one, and people are actually switching to fabric, from other sources, we've actually said this is a primary one, the secondary one which is web another second or data feed. All the other ones are not i well, used to that extent, you will be using them often. We are from pictures, yes, we can actually get data from pictures. Those are the main things we really need to know consigning the multiple data sources in Excel for us to be able to extract, input data then into our power query to transform, clean the data. I'll see you in the next lecture video. 7. Understanding Power Query Editor Interface: So in this lecture video, I'll be teaching you understanding the Power Query editor interface. Yes, very, very important. So now we are in the resource file, we have the resource file itself. The unclean data, we have the gender department table and May sales reports, just like I've explained in the download your resource via lecture. Come over here and double click and let's move into ExlPerfect. It has loaded actually, you can see the unclean data, you can see everything. But what we need exactly is to understand the Power Query editor interface. So we're going to start cleaning and transforming in the next lecture video. Let's come over here to data. Come over what to get data. So come down here to from other sources which we have the w, which we have from table range. So instead of coming over here, I know already that this is from table range. So just click it. You don't have any problem. So I'll just click. So the first thing it's good to do is that it's going to make this into a table itself. It's unclean, fine. So it's going to make it into a table itself. Okay? There's nothing like from dirty data, you can see. This das highlighted everything for me down to the end. You can see. It says, create table. That's the first thing. It's create for you automatically just preso. You can see my table has das my table has das so just leave it like that. My table has ds, it has actually highlighted everything. From cell A two to cell K 146. Very, very perfect. So when you click it, it turns it into a table and it takes us straight into Power Query. So let's see that. Perfect, you can see, it changes into a table, perfect. Then we move into Power query. So you can see it's loading. You can see this interface is different from the lecture when we accessed the Power Query editor in Ex just to access it. But this is quite different. Now we have data so you can see the grade part animal grade like before, very, very perfect. We can see everything here. Here, firstly, we have the data. You can see the data here. I just come over here, click drag this down. That just bring your castle here. You can click and drag it up. If you feel like, click what they drag it down, depends on you. Whenever you click, if you click here, it's going to show here, just summarizing this is exactly what you've clicked. It's going to show down here, very important to know that. Another thing to quickly notice is that you can see 11 columns, 144 rows. Column profiling based on top 1,000 rules, that says if you have 1 million rows, for example, only 1,000 rows would show. Let's click here click here and come down. You can see, still coming down. Perfect. 144 rows. If it's like 1 million rules, only 1,000 will show. But whatever the adjustment we've made would actually affect the 1 million rules. Just to make it very easy for us to make it easier, Excel has actually shown only 1,000 rules for us. You can see we have 11 colons. We have one, two, three, four, five, six. When you click O and drag, you'll be able to what you'll be able to move to the other colors. You can see them all. Very, very perfect. We go to click everything. But firstly, we need to understand the interface. Before we move to the top here, to the option and menu bar here, just like the ribbon we have in Excel itself. Don't forget that power query is built in Excel. Just like an add on, but it is built in Excel, you click here, you can see the arrow here. Sometimes you might have it already opened, fine. It shows us that this is the particular table we are handling presently. Okay, the particular table we are handling presently now. So if we have two tables, the other table would be here. It will be stacked on each other, to be like layers. So I can double click and change the name here. So when I change it here, it's going to show here. You can see properties, the name. Okay? So let me say new query, then press Enter. Perfect. You can see it's changed here already. Very perfect. Okay? So you can actually collapse and expand back. So applied steps very important to know applied steps. Now, when I inported this data into Power Query, sometimes Power Query itself actually would help you to clean the data because it understands what is right and what is not right. It shows us whatever had been cleaned in Power Query. You can see changed type. That is it changed the type. We have different type of data types. We have date decima number, currency, What you can do is you can actually cancel this. That is it goes away. You can see it again. But another thing you can do, just click on the previous step. When I click on it, you can see this was what was there before ABC 123, ABC 123, ABC 123. That was the data type two. Now it's applied itself. Power Query applied this particular step to it itself. When I click it, you see what happens. You can see it changes it to ABC, ABC, ABC 123 ABC. It's made the correction, but not perfect. Now let's take, for example, I come over here, you right, click on this, just right, click on it and say remove colon. It's knows exactly what you want. Just remove the colon unless you can see, removed colons. When I come back here and just click this, you will see the column is still back there because I actually click this. I'm not coming yet. I have to come to the latest the recent thing I did. When I click here, it shows me that, yes, I actually deleted it. I can actually come over and say, no, that's not what I want to do, then you can actually cancel this and it's gone. You can bring it back. Whatever you do is being recorded here, just like macro, it is being recorded here. That when you add a new file or a new table to it, it's going to go through all the steps for a particular file, clean it and make sure it's align with all the applied steps we have here. Very, very simple. Okay? So now, coming over here, we have five, which is so close load, close and load two. So the most important thing for us to know is just that just come over here. When we are through with the claiming, we close and load. When we have somewhere exactly we want to load these two. So we use close and load two. If you don't have just a close and load, it takes you back to Exhal. It just takes you back to exhale, okay? So the home, we have a lot of properties which sum you would actually find also in transform. We have split colon. You come over here, you see split colon, we have group B here. We come over to him, you see groupby very very interesting. Those are some of the most important options for us to understand. We can decide to refresh our preview, look at the different properties. Par query as advanced editor to manage, choose colons, remove colons, p rows, remove rows, very, very simple. We can actually sort ascending or descending order split colon, good by, understanding the text type, as we actually have here when you come over here. There are a lot of options that there are replicas of options. When you have them here, you can actually write click, get them here also. You can actually click here. Apart from that, you can actually click here also. There are a lot of things that is replicated. We have merge queries, append queries, if you want to add a data to this particular data we have here, we can it from file from Excel, or from databases I've explained before, from other sources as I've explained before. Okay. Another thing we need to know, understand is a transform, lot of options for us to use here. So we're going to be using some of the most important options on the transform. Also, add cool very, very important to understand. We're going to be using the Ad column tab also. Also for the view, it's very important for us to know the view also. We can see we have here the formula p when you click it, you can see this is where the formula is. But you don't need to worry about the formula or the coding or anything actually. Power coding does everything for you. You just make changes to whatever you want to make changes to? Perfect. You must have it checked. We have mono spaced, actually, the text would actually look like, you know, some typewriter text, leave it as it is. So show white spaces. It is just like saying, want to trim, we want to clean the white spaces away. So when I click, you can see, all this goes back, although it needs some more changes, but you can see the white spaces are gone, but we're going to do that ourselves, okay? Colon quality. The telling me, colon is valid, no error no emptiness. When you click Wood and drag like this, you would see everything very, very perfect. When you come over a colon distribution, sometimes we might have some serial number, actually, which we have here a serial number when we take away the rows away and actually split this colon. We're going to be having the names separate and the serial number separate. As a result of the data, the dirty data, everything is just packed together, it's going to show us if the serial number is distinct. Enough. But for now, everything is just distinct and unique, everything together. Six distinct one unique. You can see you can say seven distinct one unique. There are sometimes we actually need to use this column distribution. We can uncheck that. Column profile. Column profile doesn't really have much to show us. That is it. That is it for navigating the Power query editor in Excel, where I'll be teaching you how to clean and transform data inside this Power query editor. 8. Filtering and Removing blank spaces: In this lecture video, I'll be teaching you how to filter and remove blank spaces in Exile. Yes. So now we have the resource file. Unlike when we navigated the Power Query Editor, I'm going to input this data in a different form. So we want to open it through folder, and that is the best way because when you double click, you can see, this is the main file where we have the daty data from January to April, and this is the May file. If I want to keep on adding May, June, July, August till the end, keep on adding. I have to connect it, export it from file into Power Query. That is how it is going to work out. Now the best thing I can do is just come over here, click here, press on Control C, or you right click. Press on copy. So you can press on Window D, takes it to your desktop, come over your right click, then you then paste. So you can click here and drag it here or you just leave it the way it is. That is perfect. That's fine. Okay? So come over here, double click. When you double click on this, I can delete this. We still have copy it. So we still have everything we have here is also in the desktop. So whenever you need the gender and departments table, I can come over here and impose it into Power query but for this particular folder in the desktop, you can see, I will delete this so I don't need it. Then for May sales report, I would remove it from this folder and take it out from this folder because I only want this to be cleaned and transformed. At the end, I'm going to add this to the folder, then you could see the automatic cleaning and transforming of power query. Click on this, right click, then press on COTS, then press on Window D, then right click here and what pass. Okay, so we have it here. Click O and drag. When you come back here, in this folder, you can see just come down here. This is it. See it's normal there. I'm going to so I'm going to extract this particular folder into Power Query. This would make it easier for us to be able to clean only January to April. Whereby May would later be added into it. I can close this. You can see. I'm going to extract this particular folder, which only as January to April sales report. At the end, I'm going to add this, click W and drag this inside this and it's going to add the May to the January to April sales report, it's going to automatically clean. When you have June, July, August, anytime it is just put it there and refresh, okay very, very simple. What am I going to do now? Come over to Excel. We need an empty blank sheet, it's loading very perfect. Okay, good. Perfect. So what do we do come over to data, as usual, come over to get data, then from file, then from folder. So we're going to be using from Excel Workbook, you're going to be using from table orange. We're going to be using from folder. Select from folder, it's loading. So you can see documents. So just come over here to Desktop, this is desktop. This is exactly the folder. So just click. Don't double click, just click once, then press on open. Okay? So it's going to open it now. It's going to extract it into Power Query. That is the best way to do it because we want to add the May, June, July to infinity. This is going to look like before, because this is a folder. So this is exact you see all one resource file. This is exactly the folder, and it is just only one file inside. If they are two, three, four, five files inside, it's going to list them down here. What are we going to do? Just come over here. You can see combine and transform data. I have two, three, four files there, that it says it as data, I would just say combine and transform data. Okay? So if I don't want to transform, I'll just combine and load. Transform is for you to clean it. Okay? So combine and Load two, I have a particular pace I want to load two. So if I want to load, I can just say load only where I need to transform? So you can click Wode and drag like this, you can see everything here. I can just say transform data because that is the only data I have. But I just like to use transform combine and transform data. But there's nothing to combine, but just leave it the way it is. Combine and transform data or just say transform data is fine. It's going to open out in Power query. So before it opens, combined files because I use combined files and there's nothing to combine here. This is the force file and the foster is still the same file actually. If I click here, it's still the same thing. It's still the same thing. So the force file actually. So there are 23 files, you can actually be able to select from here. So the first file, just leave it the way it is and click on this. This is expanded, you can collapse this. Expand and you can see. This is exactly what we have in our resource file. When we open it, it is going to show everything for us. We have been able to preview exactly what we have in our resource file. Perfect. Now, just click on the Sheet one actually as it click, then press on present. Okay. It opens Power query for us. It's loading, actually. It's loading. Very, very perfect. You can see, very, very perfect. You can see everything here. You can see as I explained before. We can actually collapse this, everything goes down, collapse this, everything goes down. This is the main thing we are actually using. This is the main data were actually using. So when you click here. Okay. Perfect. We can actually close this also. The first thing you can see everything is, you know, this is unclean. What is this? This is not meant to be here. You can actually click W and drag to the end like this. You will see also that this is not meant to be here also. What do we do? It's very, very simple. The simplest. We want to filter and remove blank spaces. But the first thing we need to do here before removing blank spaces just come over here. You should select colons like this using your left mouse button. If you select here, you don't select all the colon, come over here, click Wd drag down. You have to select this particular ada here. You right click and say remove. It removes the colon for you. It's goll it's very perfect. It's very, very perfect. Click Oden drag like this. You should click this right click Remove, but it's going to take time. Press click this when you select this press on Control, select this, then select this. Then click and say, remove colons and it removes very very perfect. Click Oden drag like this. So now you can see that we have one, two, three downwards. You can remember we had an empty row before 10011. Power Query has deleted that for us automatically because it understand that it is an empty row. Perfect. Now, we would begin the filtering. What's this filter? Exactly just to say I need only when you filter something, you're saying, Oh, this is exactly what I need, and that's what I'm going to filter. Exactly what you is what you going to filter, it's very simple. So how do you do? We're going to filter just one colon and it's going to affect the other colons, because it's just like when I filter a particular row, it disappears. So all the dirty when you come down, you keep on seeing them dirty files, Dirty rolls rather. You can see and that is the way it might actually be in the M file. You can see them perfect. So what do we do? It's very, very simple. So we filter and remove the blank space. So when we filter, the blank space removes, okay? So you can see here. When you click here, you can see sort ascending order, sort descending, want to sort. But we're not doing that we're not sorting. Okay, when you sort, you arrange them organized well, okay? We're not going to do that. Okay? So what exactly you do you need to filter. So when you want to filter, you come over here so you can just select here. I deselect everything, and when you click again, it selects everything. Exactly what do I need? You can see everything we have here is here. You can keep going down. The only place that this shows the asteric and the ash shows is here, it's representing every other ash or asteric we have. I filter them out. They won't be in what in the table again, press on Okay. You can see now very clean, very, very perfect. Bring this down. You can see. You won't find anything like that anymore in this table. Filter it, it's gone. I'll see you in the next lecture video. 9. Splitting Columns, Removing Columns and Extract: In this lecture video, we're teaching you how to split colons, remove colons, and extracts. Okay? So I also split the serial number from the names. Okay? So I can do it in two ways. It is either I come over here to OM and say split columns. Okay, sorry, I have to click a colon already. That's very important. You need to highlight a colon. So when you select a split colon, you can see, or you come over to transfer and select spit column. Okay? That's the first one. The second one is for us to want to extract, so you can extract also. But first let me use the split colon. So when I say split colon, you split column by delimeter by number of characters, by positions by lowercase to uppercase, perks to lower case, digit to non digits, non digit to digit. The most important one you have to know and you have to know how to use perfectly well is by delimiter. You click here, by delimiter, it's loads. We can see this is custom. You can select or enter delimiter. The limiter is something you use, something you show that this is exactly the delimter, the limit I want the delimiter is something in between the two things you want to what you want to separate, want to split. So when I come over here and say, space, let me just say space, let's see what happens, you can see the results. You can see. After this, we add a space, then we add an iPh then we add a space, then we add the full name, the first name. Then we add a space, then we add the second name. You can see actually there were some other spaces also. That's why it's actually giving me much more colons. You can see. We had 11 colons before now we have 19, you can see, no, no. That is where the problem is. You won't just use space. You need to use custom. You need to show exactly what's happening. So just come over here and delete that organ, then delete this also, perfect. What do you do? Just come over to split column by delimiter. Then come over to Custom. When you come over to Custom, it has actually shown you exactly this particular iPhone. You can actually press on Okay. Or you create this yourself by saying space, Iphene space. It's exactly what's going to use Okay, but let's see what is going to happen. Press on Okay Okay, you can see perfect. It gives us exactly this. Let's come back again and say, let me say I want to use exactly what it showed me before, which is the normal custom, which is this, just iphon, then press on Okay let's see what happens, it splits. Very perfect. Let me delete this. It's very important. Let me delete this. Now the second way to do this is to extract. It's very simple. Just tell that, okay, I want to extract something. From this particular colon. You're not spitting, you're extracting, just like spitting actually. What is the length of what you want to extract? The first character, last character range, text before the limiter, text after limiter, text between the limiter. I'll use text before, just click on text before and what is the text exactly? What do you want exactly? Just press on space, then phen then space, then press on. You can see before that space hyphen and space, this is it. You can see the Les check and Let's after. After the space pen space, then present. You can see it remains this. You can see perfectly well. When you split colon, whatever you splitted, they remain. But when you extract, it only extracts that thing you said you needed, then deletes the other colon. It removes for you the other colon. I'm not going to be using extracts. I'm going to be using splits, which is the best thing for me to use. The limiter, best thing for me to use right now, very, very perfect. You can see we have this, we have this. I can decide to split again to first name my last name. By dlimeter you can see space. I understands before it actually gave me an iPhone. Now we're saying space. It's understands. Press on okay then it's going to what? It's going to distribute them for me. V, very perfect. You can see, perfect. But I don't want to do that actually because there are some other species at the back or at the front of some text. You can see it actually affected everything, actually affected this one also, let me bring it back and delete this and delete this. We have this perfect because there are some spaces at the end of the words or in between the words and it's going to affect it drastically. To remove colon is just like just we click N word, they'll remove. Just like we've done before, but bring it back ren. That is how to split colon, remove colon and extract. I'll see you in the next lecture video. 10. Changing Data types and Column names: So in this lecture video, I'll be teaching you how to change data types and column names. So it's very simple. So when you see anything like this here, this is what we call the data type. It's very, very simple. For this, actually, this is number, there's an number. So just click here. O number. You can see perfect is changes. Before normally when you just click A, you can see, you can see what is here. Perfect. Now when we have this, so change type O number. For this also we have it as the number word. I'll come over here, just double click here to change the column name. Just say S N. Present Enter, perfect. That's all. Sales posing just double click, just take away too, then Enter, perfect. And that's all. 11. Trimming and Cleaning Text Fields: In this lecture video, we teaching her to trim and clean the text fields. That is the columns, actually. We need to clean this two. I can click here and press on Control and click here. It's going to be cleaning the two automatically for us. So how do we clean? Come over to formats. Because normally in Exl when you trim, it helps you take away the spaces at the beginning and the spaces are the end, just come over here and just trim. Let's see what happens. You can see perfectly well. When I come here and click this, you can see. You can see those species here before species were also before. Just one space is here before you can see. When I click here, you see them. You can see, very perfect. There are still more spaces, so it doesn't recognize the middle spaces here. But the spaces at the beginning and at the end, it actually make sure it cleans that for us. Apart from that, we can actually use the clean also, just clean it actually just to make it well cleaned. But the most important thing is the trim function, and we've used that to take away to eliminate the spaces at the beginning of the words and at the end of the words. 12. Replace Values: In this lecture video, I want to eliminate the spaces in between. That is the most difficult one to do. How do we do that? It is very simple. I've selected it to still the same thing. Come over to OM. Okay, click on O and you press replace values. Okay? So spaces are also values. Okay. So when you click here, okay, I would use the space bar twice or twice. Let me try the trice, okay? So one, two, three. Okay. Then replace with what one, one space. Then press on o. So you see how this one is going to, like, you know, be reduced. This is going to be reduced. It's also going to be reduced, okay? So press on Okay, and let's see what happens. You can see perfectly, it strings, it takes away two spaces for us. Apart from that let's reduce it to two now. Spaces one, two, then click here, one, press on. Okay. You can see, very, very perfect. Almost done. This remains just like, you know, just one more. Then here, all done, very perfect. There might be some eating actually, so we need to do one more. Come over here to replace values, then press on. One, two, then come over here, release this to it. One, then press on. Okay. So very very perfect. A done? Yes, all done. It's getting cleaner and cleaner actually. Everything is becoming perfect, okay? So another thing if you actually notice is that when you come over here, you can see the space here, it's not gone, because Exel actually understand that oh, there should be one space in between words. Okay? This is no words. This is just one word, okay. Apart from that, we have this household item. There is no space in between the D and the I. So we have to do that manually ourselves. So it's very, very important. Come over here, instead of me coming here and say replace one space with no space. It's going to affect every other thing we have here. This will affect household item, household item you can see. So I'm not going to do that, so I'll just delete it away. So what do I do? I'll come here individually. Click here. Then what do you do? You can actually write click you can see, replace values or come over here. I'll just say replace values. I have this here, just copy contro C, copy, then paste here. What do you do? Then delete the space, then press on Okay. So it does that for us very perfect. Apart from that, we can see the household items here. This is one. Let's go down. We can see This is two, come down more. You probably able to see, you can see three. So no matter what actually, we just come over here two. Instead of doing this manually, let's come over to replace values, say, I old items, make sure you spell it very well and Control C, then come over here, then paste, then you give it what, give it a space. So we present, Okay. So when you start checking one by one, you see, it's all normal. Everything will be normal, very, very perfect. You can see some places are capital, some places are small letter, we are going to adjust that also, very, very perfect. So anymore, you can see this. Oh, sorry. So just right click on this. Replace Values, copy, then past, then come over here, then press on Enter. Perfect. So just keep coming It's just go up. If any other one. So this is where the problem of, you know, individual corrections is. You can see this also, right click on this Replace Values, very perfect. But most times you might not see things like this. I just decided to just make this particular electronics have some spaces in between just for us to be able to know exactly how it is meant to be done. In most cases, you won't see things like this in your ex or fire I probably you see them, so this is how to solve them. That is it for the replaced values. I'll see in the next lecture video. 13. Text Formatting: This lecture video, we teaching you text formatting. We can actually see actually here and we have the first name with a capital letter, the last name with a capital letter also. Everything is well. Arranged. But for here, we have capital small or small title case, title case, title case, Capital. So you can see small. So it is normal arrage. Let me just click here and click here also just in case so that we don't have any mistake here also. So come over to what come over to transform, we have format, so we can see lowercase. So it makes everything lowercase, very simple uppercase. Instead of using formulas just like in normal Excel, capitalize each word. This is exactly what we need, and that's all for text formatting. 14. Merging and Re-arranging Columns: In this lecture video, we move to merging and rearranging columns. Very perfect. But before we do that, we need to come over here. Then what do we do? We replace value. We can see the replace value. When you come over to home also, you see the replace value, so I can actually, I can click here and say 25, press on tap to 2025. It's very important. That is the date. Very very perfect. I can actually merge January with what with 2025. But before that, let us rearrange the column. I can actually click when you want to rearrange, you can just click Oden drag like this. You can see just drop it. Very perfect. So we are the sales month, the financial year, the sales day. Perfect. You can just click here. Click Woodn Drag, bring it. You can see the green line, you can see this green, this particular green highlights, something like that. Just bring it here, drop it here. You can see the sales day, the month comes before the year and the day comes before the month. Very, very important. So I can join January February. Then January February with 2025. So I can merge the colons together. Click here, press on control, then click here. What do I do? Perfect. I'll come to transform, merge colons. She's going to ask me the separator you want to use in merging the colons, the separator. What do the separator? I can just say come over here. I just say comer, a Cammer. Then I can say, Okay. So let's see what happens, you can see, Friday, January, merge together. I can also merge it with this. I actually highlighted it two already for me. Merge colon again, then say come. Come and press on. Okay. You can see very perfect. So it's actually one we merge this. I'm not going to do that. But the most important thing we need to know is that when you put a comma, you need to put a space. So how do we do that? Come over here, delete this, delete this. So very perfect. The two have been highlighted already, merge columns again, come over to Custom. When you come over to Custom, you come over here. Press on come on, then put the space. So press on equals to the presento rather. You can see Friday, January. Very very perfect. But this is not exactly what I want to merge. That was just an example. What I exactly want to merge is what the month and the year together. So you press on the salesman, press on control, and press on the financial year. So merge columns. So come over to what? Come over to custom, then put comer, then space. So most importantly, another important thing you need to know is what we are what we call new column name. So click or you just double click, okay? So we have the sales the sales day. Sales we have sales month financial year. So we need to give it a name because it's a new column because column that is going to be merged, have to be given a new name. I will say, by none. Shall month end here. Prey that okay let's see what happens. You can see, very perfect. Financial won't end here, January 2025 going down, we have February 2025. We have March 5, we have April 20, 25, perfect. I like it like this. It's okay. It's something I want. Sales day, Friday, Monday, Monday, fine that's perfect. Financial month and year I want together. You can see it's getting cleaned already. Very very perfect. 15. Adding new calculated Columns: This lecture video, I will be teaching you how to add a new calculated colon. Let's click Old and Drag. We're done with all this. Actually, it's becoming clean and better. We have the total unit sold. We have the total cost. We want to add the revenue for us to get the revenue, you know what to do, right? Okay? We need to multiply the total unit sold by the total cost. Okay? Firstly, let's click here and let's say we have the old number or want to use the decimal number, it's fine, but it's more preferable. We use the old number, okay? Yeah, replace current actually. A number. Perfect. Let's come over here and say currency. We need to identify that this is a currency. It's decides to put the decimal points. It's fine. Perfect. Now what do we do? Just click here, press on Control and click here. So we want to multiply. Perfect. Now we are in the art colon because, as I said before, we are adding a new calculated colon, okay? A column that comes with a calculation already, as a result of us multiplying, trying to get the revenue. Perfect. When we come to statistics, that's now what we want. When we come to standard, that's exactly what we want. I want to multiply. When I select on multiply, it's going to give me the revenue, see? Perfect. This is you can say multiplication, but when I click on and drag, can see nothing shows here. Whenever you add a new colon, it adds it at the end of the colons. You can click O and drag it. Come on keep on dragging like this. Just keep on dragging. You can see. We have the total, we have total units sold, we have the, we have the total core. So just put it here. I can see the green highlights. You can see that green highlight, so just drop it here. Drops it moves the customer region to the right. You can see. Let me double click here and press on what and say revenue. I'm going to change it revenue, press on enter, very perfect. Now we have the total units sold. We have the total cost of one and each individual product. Then when we multiply the two actually, we have this as our revenue. You can see? Very, very perfect. That is actually another way to calculate and add a new colon. 16. Data Formatting: So in this lecture video, I'll be teaching you dates formatting. Yes. Well before that, let's actually edit this, okay? Just come over here. I need to replace this. So come to transform, okay? Come over to replace values, okay? Then I need to replace US with USA, present tab, USA, then pres on. Okay. Very perfect. You can see the roles are becoming very, very neat already, okay? Perfect. So now we are talking about dates formatting. We have the order dates and we have the payment date. You might actually order some goods from Amazon and Temo and some other platforms. So we have the or data, we have the payment date. So when we need to stop track yes. We need to subtract to get the days in between the other day and the payment dates. Normally, we should have actually said delivery dates, because this is a type of delivery whereby the date is being delivered to depos and then you pay, something like that. I should have been delivery dates, but it's fine like this, okay. So from the payment dates, so you use the highest subtracted by the lowest. Sorry, you subtract the lowest from the highest. That is let's say 200 -100, something like that, you get you use the latest dates, you subtract it from the what from the former format dates, the later from the former. It's very simple. As we've been doing, just click here, the press and go through the click here. But we have a problem. When we come over to when we come over to O nothing, we don't have any dates, transform, we have the dates. But this is the only thing that's showing. So when we come over to Add colon, the best one is for us to use the add colon. We want to add a new colon that actually shows us the dates. The date is blood. It is gray out. What happened? What is wrong? Let's look at the text formats. Click here. This is an alphabet. You need to click here and see what's dates? Very important. You can see, you come over here, also click here and say dates. Very, very important. When you click here, press on Control and click here. You can see it is normal grade out. When I click here and I say subtract, you can see everything is grade out except from subtract dates and latest. When I click over here, the only thing we need exactly is a subtract das so that when someone orders a particular product, we know how many ds is going to use on the ocean, on the sea, or the cargo plane, whatever it is. Click here. I actually shows the colon at the end as usual to always be at the end. Come over here, then double click. Let's say days before before before delivery. Enter. Perfect. You can see, that's very perfect. Everything has been cleaned already from the beginning till the end. I'll see you in the next lecture video. We'll be joining combining a new data table with this cleaned data table we have already. I'll see you in the next lecture video. 17. Merging Data Tables: This lecture video, I'll be teaching you how to combine or merge data tables together. Yes. Out of all the colons we have here, we need another colon to join with this colon. We need a department colon to show the department each sales person is and also the gender colon to show the gender of each sales person. How do we do that? It's very simple merging of tables. Come over to home, then come over to new source. File. Excel workbook. It's very simple. Just select Excel workbook and it takes us to our documents. Exactly what do I need? I need to come to downloads. This exactly this is what I need exactly. Double click here. Perfect. Then come over to resource files. Double click resource files, then gender and department table. You can see. Resource file is there for you to use. Click here and input. We can see it's loading, it's loading. Very, very perfect. You can see no items selected for preview. Yes, the table inside the Exhal file is called the Sheet one. It has not been renamed, but it's fine. Just leave it at sheet one. You select it, you don't need to come over here or select multiple items. You don't need that actually. If you collapse this, if you expand, it shows you back this one. That is the file itself. But this is the table. When you select here, you will see the table we have. We have just three colons. We have the cells person. It's very important for you to know that you can we have only five rows here. Excel will distribute everything you have here. For each person. I mean, Power Query would distribute everything you have here for each person. So far, the salesperson is correct. We have Logan Richardson, we have Ava Copper, we have Charlotte Baker, we have Oliver Foster. So far, the four of them are present here, but in multiples. Anyway, it sees Richardson Richardson Richardson Richardson is going to apply female. Anyway it sees the three is going to apply male. Anyway it sees Richardson is going to apply it. It's going to tell us that she's where she's the sales operations department. For Copper in the customer success department. For Baker, in the account management department. For Foster in a business development. It's very simple click here. You can see I've selected here. Then you can see presento Perfect. You can see. It's the other one has disappeared, no. This is the table three we have. Let me just double click and say query one. Sorry. Query one. Then let me double click this also. Oh, sorry. Query two, press enter. Okay. So this is query one. This is query two. There are two different tables entirely, okay? I want to met them together. I want them to be one table. Because when you open a new table, when you open a new PDF and new sees with new text, they they're going to be layered. They're going to be stacked here, just like layers. So now I want to join them together. You can see I want to join the two of them together. And it's going to distribute the departments evenly and the gender equally. Come to the first one, which is query one, okay? Okay. So you come over to what merge queries. Very, very important. You can see. Select a table and merge color to create a merge table. Okay? So this is the first one here. This is the first table query one. So you select here and select telling me query one current. That's a current one. So we can join. This is the same table, so we can join the same table, okay? So we need what query two. You can see, very, very perfect. Power Query wants to do something very important. Power Query needs, before you can be able to join two things, there should be something similar. That will be in the first table in Equery one and there will be query two that would attach the two of them together. They must be similary able so that they can be linked together. What is the similar thing we have here? It is what the salesperson, just click and click here. If you click here, it's going to leave, it's going to go away from the first one. Click. You don't need to use control or space or shift, you're good to go. Okay? So you can see, we have this and we have this. They are identical. Even if we have ten Logan, even if you have ten Richard saying 1 million copper, 200 Baker, fine. So far, they are related. So far, they are similar. That is the link we have to use. You have to click this and click this. So that is the link. So what is the next thing today? Apart from that, join kind, we have what we call inner join and we have what we call outer join. So outer join comprises of left join, right join, full join, but for us to be able to understand this, it might be quite complicated for us, but the best thing is just leave it the way it is. Okay? Just leave it the way it is. This is left outer all from first marching from second. Just leave it the way it is. It's perfect like this. Okay? So that is whatever we have here as the second table with the query two would start from the word from the right. It won't start. That is this small table will not start from here from the left, it will start from the right. Okay, left out, all from first, marching from second. That is all from first will be in the left while the second being what at the right. But if I changes to what to right sorry. Changes to right. That is all from second, marching from the first. That is what that is everything here would actually comes from the left. It will come to stay, it will start from the w. From the left. But I want it to be start from the right here. It might be confusing, leave it the way it is left outer. If you select on this, it's going to give you much more. You come over here and select this. Look at the selection matches 134 or 135 rules from the first table. You need to make sure it matches 135 or 135, no error. You need to select this. So you can see the selection matches 135135 rules from the first table. Very, very, very important. You need to check here. You don't need to use this, just leave this the way it is. There are other advanced options, but leave it the way it is okay, perfect. We've done everything perfectly well, then press on. Okay. Good. So it's going to add at the end of the col. You can see, as I've said, left, but it's going to actually that is the first col is going to be the left and this one is going to add to what to the right answer. It might be confusing, but just use the left. Use the way up to, okay? So you can see table, table, table, table, table. What is wrong? Okay? So you can see this particular icon here. It's for filter, right, filter and sort. But you can see, you can see how everything actually looks like. But for this, it looks different. So there's something ding, right? So when you click here, so you can see it's telling us to expand. This is where we're going to words expand. You can unselect this. So what exactly do we need? If you could remember perfectly well, the first colon was Watts was the salesperson. We have the name for the salesperson. We have the salesperson's name already. We don't need it. So what would we need? We need the gender, which is column two and the patment which is column three. In the El file, it was not changed. We're going to change the name when we have the table. Press on Okay let's see what happens. You can see this before delivery. When I click Old and drag like this, you can see, very simple. When I come over, I just double click here and say, gender, Enter. When I come over here and double click again and say the patmentEnter. Perfect. I need to bring gender and department. You click Press on Control then click this also. You click on the drag it. I want to take it beside the sales person name. I want it to be beside it, it's moving. It's moving, very, very perfect. I want it to be around here. We have the serial number, spsing, gender, department, then the product sold, you can see, very, very perfect. We're able to learn how to merge colons table one table in power query, that is we got table from outside, a new table entirely, and we're able to merge it combine it with this particular table. You can see it is getting cleaned. I'll see you in the next lecture video. 18. Loading Data into Excel: Now we are done with the cleaning. Everything now is very perfect. You can see, everything is clean, neat. Everything we need actually easier right now, we need to load it back into Exhale back into Exhal. How do we do that it's very simple? Just come over here to close and load. So just close and load straightforward into Excel or close and load to somewhere in Exhale what you have? But fine, just say close and load. It would wrap everything up for you, click. And it loads into El for you. Very, very perfect. You can see everything. You can see this is query two, which we added. This is query one. You can see everything. This is Sheet one. Sheet one is just just empty. So you can just write click and delete. We can actually press on Control and use a minimum mouse button to zoom out just for us to see everything, but that's too much. This is okay. You can come over a click wood and drag this like this. Or you can close it down. So if you want to open that back again, just come over to data and you see what you see the query and connections, just click it. It comes back, close it. It's fine. You can see everything very, very perfect. You can see very, very perfect. Everything looks neat from January to April. You can see. We don't need this query two anymore, just we click on it and delete it. We don't need query two, delete. We don't need query two. It has been added here, you can see, we have the gender and we have the departments gender. Departments, very, very perfect. Now we want to go back to Power Query and adjust some things, just press on queries and connection. This is query one. This is query two, query one, double click on query one. It takes us back into the Power Query editor for us to make some new adjustments if we want to. You see that now, okay? You can see, it's back inside Power Query. If you want to go back again, just go back and say close and load, and that's all, that's fine. We have it already just back here. You can actually close this. The next lecture video will be adding the May sales report to this report. I'll see you in the next lecture video. 19. Adding the 'May" Sales Report: This lecture video, I'll be showing you how to add the May sales report to the January to April sales report. You might have June, July, August, September later on, add them into the folder. It's very simple. Come outside here. Here, don't forget we have this folder exactly as we've done before. You can see. I'm going to click this Click O and drag this inside and drop. Just click and drag it on top and just drop it or you copy and paste it, it's fine. Come down back to Excel. I have added that particular May sales report, that file into the folder. What do I do? Just come over to query, press on query and do what and refresh. You can see we have only April here. I ended at April. Then when I refresh what happens, it would add May, you can see it has added MeFus May and you can see This is the only sales reports we have for May. So there's only for me to go back to Pa Qora and start editing and editing. So I cleaned everything here. You can see it's in line. It's added department and department and gender. Everything has been added, the calculation, the revenue calculation, the days before delivery. Very, very perfect. Okay? So for the total cost and revenue you can click Wonrg just come over to home then say currency. Adjust more. Okay? I want to change the currency to come over here, change the currency to dollar, come over here, click here, then change to dollar here. And that's perfect enough. So I can decide to remove the decimal points, okay? So just reduce it here. And everything is good to go. So you can see, very, very perfect. So we have the January, February, March, April, May sales report. So we have the June, July, August sales report, we just come over here and then just, you know, paste everything here, you can see, this is the May. So we paste the June, July, August, September, and it keeps on adding into the Power Query, then make sure you come over to query and click here and you refresh. When you refresh, actually, it adds to it. Automatically, you don't need to go back and go back and be doing the boring repetitive task again. Power Query automates everything for you. That is it about power query.