Microsoft Excel: Learn Power Query Basics!

1. Welcome to the Power Query Course!: Hi, Welcome to Microsoft Excel. R3, learned the basics. So you're starting out and exhale parkway that this is perfect for you. What you will learn from this course are to transport 30 data from this data folder, workbooks, merging data in a whole lot more. So nerd part for you now and see you inside. 2. Excel Power Query - Introduction: Hi, this is Brian Holland. Welcome to the wonderful world off Park Worry. So I'm currently using Excel 2016 and this is called Get and Transform. But in Excel 2010 and 2013 it's actually called Park Re. So I'm not sure why they have different names, but they mean exactly the same thing. So for park worry, it's all about transforming data. So before we start, I want to show you a very quick demo off how we can use Park Re to trim the data inside this table. This table has a lot of extra spaces on the names, and we want to remove them using Park Re because you might be wondering words are park where your window and to be able to show that we need a data source to be our starting point. So to do that, we need to make sure that we have our excel table selected, and I'll just select here from data from table inside to get and transform, and it's gonna be showing a separate window where in Park Re will open and this is where we can perform. Our magic in part were. So if I just move this away. You can see that you still have your workbook open over here and you have a separate query editor window where you can use the functions of Parker and you can perform your data clean up in here. And one of the things, the important things that park where is because you might be wondering how come we have to separate windows, This one on this right side on the Corrie editor We're not touching our original data, which means it's a nice playground for you to do, right to perform your operations. Just play around with it and you won't even touch the original data in the process. So which means if we trim this one, it won't take effect here. So now a copy of your data is being shown here. So what we can do is let's just right click here, go to transform and let's Elektrim and not a here on the right side, we have the applied steps pain. So any steps that you have performed in the query editor window are listed over here, which means it's like a historical view off all of the steps, every single step that you have done in your create, and you can also modify them so I could see here we have the delete icon, and then you could just move them of swell all around if you want to change the order of the steps. So which means it's a up in book Marie, and you can make your changes or play around with it. So, as I said, it's a playground for you. So just feel free with your imagination on how you want to clean your data. And once we're happy with, the data will go to home clothes and load. So what will happen here is the clean data that you have done over here will be saved as a separate work inside your original workbook, which means you're not touching original data, but you have a new work ship that is just a copy pace off. What's the output over here? Which is really cool, right? And we have the query editor window. There's a lot of stuff in here, but don't worry in our next video will cover that in detail on what each function will do. So once we're happy with the data, let's go close and load. And now we have a copy off this clean data inside a new worshipped 3. Excel Power Query - Query Editor Ribbon: Hi, this is Brian Hung and welcome to the Wonderful World off park worry. So before we dive into the details of Park, really want to give you a quick overview off what park? Where yourself about So what we have here is actually a Finnish example off where we use park re to transform our data. So, for example, this one has a list of names, and then we just use parkway to trim it and give us a result off clean data. So first things first is what it's park were all about. So park worry is all about cleaning your data. So it's really good in terms off getting your dirty data from different sources, for example, and then cleaning them or transforming them into a different form that will be useful for you when it comes to that. Say, creating reports out of it are creating a pivot table out. If it that's what Park re it's really useful for. And another thing is, if you have different sources of data and you want to aggregate them to get her, let's say you have sources from a database that a an access database or maybe another Xel file or text files or even from Web services. Right? When you get all of this different sources of data, you can aggregate them to get her using park we into one central location or data model, and then you can perform your clean. It steps on it, right? So what we want to show you here quickly. ISS The preview after the query Editor ribbon in Parker. So here's what I'll do. Go straight to data. Let's go to show quarries. I'll destroy Click an edit here and we will be taken straight to the query editor window Where in I performed a step using park worry when it comes to trimming the data. So what? I want to show you here first. It's just a like an overview of what the functions are like for the home tab. We have clothes and load, so once you're happy with the result inquiry, then you could just close and load and the resulting data or to clean data, I would load into your excellent worship. But before we proceed the other functions over here. I want to show you first over here that Chris Settings and you could see here on the right side, the plight steps and this really cool Because whatever steps that you have done in park worry it will all be listed here step by step. So which means if you make a mistake along the way, you just go back to that say, step number five or step number tree and make changes from debt, which means park where he is very lenient when it comes to experimentation. It really encourages you to experiment and use all of the function so that you can get your desired result. And if ever you make a mistake, you can just go somewhere in the middle, delete it, modify it and then you could just replay the steps that you have just did. OK, so let's go back to the home tab. So we have choose column. So what columns do you want to keep? Right. And then we also have remove columns so we have remove call off the ones that you selected or remove the other clubs that you have not selected. You also have key pro, so you could think of it as similar to the column counterpart, right? Whatever you say. Like they just keep those roles and then same thing for remove roles, which once you've selected, then just delete it from your data. And then we have sorting. We have splitting of column, so we have splitting by the limiter or explaining by the number off characters. So the limiter, if it's a comma, delimited valued and you can split it to multiple columns for other the limiters that you want to use. You also have a number of Carter's. So if you want to split by Tree Carter's or five cartridges, and you can do that too slow gripped by, we're gonna be discussing. It's more in detail in one of examples in our course, and then we also have data types, so there's a lot of data types that you can support. So if you want, Park reads, Who set the data type for a spit of condom than you can do it. Here we have currencies numbers we have days time threat and then use first Rose headers. If you want to use the first row, ask your column, header and then replace value. So it's kind of similar with a fine and replace right and then we have merge queries and a pancreas, so we were going to be discussing this in detail. It's well, this are really cool stuff that you could do in park Worry. Let's go to the Transform tab. So we have gripped by already mentioned this. Use first ropes headers yet transpose. Okay, So if you want to change your table layup from rows and columns, you're gonna transport or transform them into a column row order format, then we have reverse roles. You want to reverse the order count rows, count the number of rows that you have set the data type from here, right? We have rename. If you want to rename that column right, and then we have replaced values. Replace errors. If you have errors all over your table, you can replace it with a more friendly value or clean value their field. This is actually one of the coolest part as well. So if you have, let's say a value over here right and then black values downwards. You can use Phil down, for example, to populate the rest of the null values with whatever the not empty value that you can see . For example, if it's such a cool over here. And then you have four empty values over here. Those empty values will be populated with Sachiko. We're gonna be using this a lot in our examples in the course. Okay, so we have people color. We haven't people columns, right? We have moved. If you want to move the columns Two different location split column really discussed this format, right? If you want to transform it to lower case upper case capital outage work, it's critical. We have cream clean ad perfect. And Suffolk says, Okay, do you have extract right? Okay. And then we also have parsing. It's also cool if you have the Jason format or XML format, right? If your source contains that, you can parse that as well. We have merge columns. And if if it's a numerical column that you have your basic mathematical operations that you could do and same a spell for date and time clubs, for example, if you want to get the hour complaint or get the minute or second right, you can do that a swell. And there we have add columns at Custom column, right? So if you want a specific formula that you want to add, and then, based on the different column, we can use that ad index column. Okay, so let's just try this out. So it's like adding your numbering to the ropes right and cold in the park for is we could just do this. And there you go. We have duplicate column. So whatever you selected, do you want to duplicate that one conditional column? You could think of it as adding an F condition. If you're familiar with the if formula and then we have format already saw this one of all ago, and then we have extract parts fell and the American operations and also the date and time operations last time would be the view so you can change the layout, right? You could just hide the formula bar and then another one that I want to show us the advance editor. So this worthy inner workings or the details off what your park three or your specific Cory is doing so you can see over here step by step. Well, we won't be going through this in detail, but they just shows you on water deck. Sex stepped or you could think of it like the code on what's happening inside your query. It's just something really cool to see. Okay, so that's the quick overview off ECRI editor or the queer ribbon right that we use in park free. So if this is a bit overwhelming for you, don't worry. Because in our detailed examples were going to be discussing more practical examples of park we on how we can use these individual functions over here and combined them into something really useful and cool. Okay. And I know you might be thinking as well right now that hey, this individual functions over here. Just do it in plain old Excel, right? Yes, you can do that in excel, but in Park Re will be showing you in detail the power when it comes to combining all of them together. Yes, each individual function might not be asked useful as you think, but once you start using them to get her in transforming and cleaning your data, then that's the time that we will be able to appreciate park worry for about it. This Okay, so stationed. We have a lot off material to cover and examples to cover that we're gonna be showing you really examples on how we can transform dirty unuseful data into something that we can really use. 4. Transform Data - Trim in Excel Power Query: Okay, so for this example, over here, we're gonna be using the dream of park worry. And in this one, we have this table of names so you could see here we have a number of names, so if you scroll down, there's around 60 names, and our goal is to get the counts off each name, and we're gonna be doing that through people tables. Okay, So what I'll do right now is let's go to insert and go straight to people table. So let's just use a new worshipped and go, OK, right. So I'll distract the names to Rose and let's get the count off each name. And if we look here to something that looks out at the moment, if we have a look at Arlene, there's gonna be two rows of our Dean's over here. And that's not what we're expecting, because we would expect the Arlene's here to be combined to a single entry, and the count would be treat. So if we go for a deeper look here, if we look here at the Guardian and came no spaces over here, but if we check the second Arnie in, you could see that there are spaces at the further end. So which means we need to do some data cleanup before this is going to be useful for us. So let's go back here and let's use park. We on how we can trim the extra spaces so that once we would do our people table, we're gonna have clean results. OK, so to be able to do this in Parker, it's go to data, makes you selected your table and let's select from table. And once we do that, what's gonna happen? ISS The Query editor window will open, and our data will also be placed in there. So we need to see the entire names table to show up inside. Okay, so now we could see the queer editor window running and loading so we could see our table. That's well here. So if you scroll down, we should steed the 60 names of Swell over here. Okay, now it's looking good at the moment, so in the next thing it's let's go through transform, and then let's see on where our cream method would be. So let's go to format and then you could see here that trim functionality is in here. So what we can do here is just make sure that the column is selected, which is really highlighted at the moment. It's go straight to format select trim, and he could see a swell on the steps. You could see that it's trim text over here because that's what we did at the moment. And now we have our clean data, and once you're happy with the results, just go back to home and then select clothes and load. So let's do that. And now what park we will do? It's our results will be loaded in a new worshipped and to see if we can do the same people table. Let's go again to insert people table. Now, based on this data over here, the clean data. Let's create a new worksheet, right? And then let's do the same thing over again, struck names to rose and then get the count of the names by placing it inside values. And you can see here that Arlene now is clean because you could see that there's only one early in this being showed up in the people table and the Countess Street, which means all of the data over here is already clean. But the next question right now is what if our table right gets a new set of names like gets additional names in here? So, for example, what I'll do is let's go back here to the original data. Okay, so what I'll do is and it's just copy a bunch of names. So that's just highlight this one. I'll copy. And let's just add the names in here. So the next question right now is what do I need to do? Do I need to redo all of my steps in Park three to clean the data and then do the pivot table again based on the new set of data? The answer to that ISS. You don't need to do that over and over again. This is the Beauty Off Parkway because we've ready defined our crease if you go back here. So let's just go back to the results over here and then since we already have our qui, that's so you could see your still 60 rose. Right? So the beautiful thing that bought park for IHS, you will be able to redo the same steps on your source data even though your search data has been there. So what we mean by that is let's just go here and then do a refresh. So, for example, if you have this window close right to be able to open that again, let's just go back to data show Crees and you would be able to see off the queries that you have created in power. Khoury. So what I'll do right now is I'll just do a refresh and you can see here it's now 77 rules loaded, and the new set of data is cleaned, which means the steps that it did or we did right in trimming power. Cree redid the same steps on your latest set off data off source there. And if we go back here, if we just right click here and do a refresh, its cookery fresh, you could see are only now. It's fine because we copy paste it a couple of irony in here, but you could check a swell that the data is clean because it's just a single Ardian and everything has been trained already. So that's the beautiful thing With Park Reace, you just do a refresh and park. We will just re execute the steps without the need for you to redo the same thing over and over again. 5. Transform Data - Format Dates and Values in Excel Power Query: Okay, So for this example, we're going to be discussing more about the data types in Park Re. So we have here this table, right? We have the date column can see over here we have the name column, and then we have the payment, caughtem. Okay, So usually when we get data from different sources, sometimes the formatting, for example, or the date like, for example, you can see over here is not in the format that we expect some how their date is in a American format. So if we're gonna be doing some analysis on it, our reporting on it, it's not gonna be very useful for us, right? And then for the payment, if we want to make changes, fell to the formatting as well. You can do that in Park Re ask your initial steps. Okay, so here's what we'll do to be able to going straight to partner is just go to data and then let's go from table. OK, so we'll open up the query editor and we have your table loaded right away. And the cool thing here is it's very easy to be able to change data types in Park re you could see here that date, for example, Park where he was able to infer that it's a number. I mean, you can't blame Park were with us. It's definitely a number, right? But this is not the format that we want. So let's change this, the one to treat its just click on it and then change it to a date. Cool. You could see here that the format now is already correct, right? It's in the date format. Name is perfectly fine. So if you just click here to ABC, you could see that it's a text. So that's working its expected. We have the payment. So we Let's just check this one. We have decimal number, right? So, for example, if you're not happy with the decimal number, it's changed this right click on it and then change it to currency, and it will just change it to the currency format. And once we're happy with this, so we have the data and you could see us well into right side that we change the data types , write the column So it's loved a swell or this that over here. So what we're gonna do is go to home. Close and load. Since we're happy with the data and then our clean data now will be loaded in new work. Shit. Right. So we have the dates payment a swell looking good and ready to go. 6. Transform Data - Parsing URLs in Excel Power Query: for this example, we're gonna be using park. We on parsing the you are else listed over here in our table so I could see here. There's quite a number of girls who have here, right? We have the fake your URL dot com. Right? So it's just a POTUS link over here. But we have here. There's a pattern could see here that it has ceased eight and then followed by Alabama. We have the name and the national name Barry Cheragh. Right. So if we just scroll down what we want to do, Yes, this is our goal. Our goal is to parse the girls over here and extract the information that we want. So for example, this one, he want to get the category, and then we want to get the value a swell. So we have state Alabama have named. We have Ashton, right? And then for this one, we have name and Barry take notice. Well, there's a couple of euros over here that are dirty data. So we have this one like the wrong We have to do this, right? So what we have It's just name and states that we want to get the data from. If you just scroll down, you could notice that all of them have names and states and there's a couple off data that are unclean. So our steps that what we're going to do in our Korea's first, we need to remove the dirty data. OK, and then we need to get the category and value. Okay, Now, we defined the steps, so let's go straight to park. Really? Go to data. Okay. Sit at this table. Make sure this moment selected, and then we have from table. Let's go straight to our query editor. Okay, so now we have our girls lifted out over here. So first experts, we need to clean our data. Right? So we're pretty sure it has a name or state and the other ones that don't have it, we need to remove it. So he would just click this down our over here too. Use the text filters. So go to text fielders, right? And then go to contains. So we're making sure that our your ill should contain either name right or it should also contain state. Okay, so we're sure off this two values if you go OK, you're going to see that the utter dirty you are else are now removed. Okay, so if you scroll down, Yeah, Looking good. You only have state name. Now, since our filtering is done, we can now start to parse or get the information that we need for the question is, how can we do that? And if you see here, we're gonna be defining a pattern finding a pattern in here because, for example, over here Alabama, right, there's always this slash and also snatch a swell that we can use when it comes to extracting our data. So what we're gonna do here issues slash to get the value first or the right most value over here, which is Alabama. We just absent vary Cheddar California. Okay, so we're gonna be using split column by the limiter. Okay, so we have defined are delimited, right? We were seeing that It's a constant slash over here, so we're gonna be using that over and over again. And if you check this one, right, the limiter, it's not here. Slash. So we're gonna be using costume views cost on. Just type it in on what you want to use and this word. The crucial part lice. It's gonna be asking you. Okay, You want to split it? But where do you want to split your slash Over here? You're the limiter, Okay? We're just gonna be using the right most limiter, because if we use the right most and it's just gonna be looking for the right will slash over here and then split it, okay? So that we can get the value. So that's looking good. Okay, let's just go. Okay? And boom. You could just see here that it's pretty pretty easy, right? It's just amazing what part where it could do. So now you have the values listed out over here. Next question, Yes. How do we get the category? So if you see this one right now, we can do the same thing. You could just use the slash again, split it from there, and then get the category value. So let's do that column right by Demeter. And then what we're gonna be using yes, cost him again and specified. Slash. Ask your deal emitter, But don't forget, since we're doing this at the right most limiter, make sure that selected the smell. Okay? And now you have your category listed out over here, and it's just really cool if you scroll down, you have all of your values over here, right? And for this column, we don't need this anymore, right? For the fake you are l So we could just right click on the column header, go remove and then just double click here and let's give it a more meaningful name but angry and the same a slow for this one. Just click and type in a value Cool. And now we have everything extracted over here. So we have this category state name, right? And then we have the value of Obama. Astronaut Cheddar were happy with the data and you could see everything that we did a swell overhearing their plight steps. And once we're good, just go whole close and load and it will now love everything in the new worship with your clean data 7. Transform Data - Split Text Fields in Excel Power Query: Okay, So for this example, we're gonna be covering on how we're gonna be doing text field, Parson. Okay, so this is some more complicated example, because we're gonna be doing mawr transformative steps using Park Re here to achieve our target state. So what we have here is we have a list of labels, right? So it's like product label. So we have bubble gum. We have meant shampoo. Right? We have, honey. And then you could see here there's some sort of product code that's in front, and then we have your product description, and then we have your product size over here. So, for example, we have four pack. We have 300 milliliters. You have 300 ounces chair, right? Okay, So here's our goal. Our goal is to split the labels and have a table that has the protocol that has the item like the item description. And we have the science as our last Cotto. But there's another catch over here. Another catches. We want our size, right. There's gonna be a space additional space right between the numerical value or the numerical size and the unit off measurement. So, for example, we have four pack over here is gonna be four space peck and then treated minute letters. We have 300 space and male cheddar, right? So a site from splitting our data to form this table, right? We're gonna be doing another transformative step on our side to swell. Okay, so let's go straight to part three that select the table, go to data and then select from table. Okay, Now we have our data loaded here in our Cree editor window. So the first step right now is how do we get the code item? Right? Description and then the size. So what we can see here is just look for a pattern. First it's gonna be the space, right? So we're gonna be splitting this by spaces, right? But we're not gonna be splitting by all of the spaces. And why do I say that? Because the bubble gum, for example, has a space in the middle. The main shampoo has a space in the middle. Right? Because if we do like splitting by spaces for all of them, then we're gonna be having additional columns because of the bubble gum or a mint shampoo are the shaving cream or the hand lotion, So we're gonna be doing a different approach. So we're just gonna be splitting first by the first base over here. Okay, so let's try it out. Let's go to split column by the limiter. Okay, so we have our space over here, and let's just use the left. Most limiter. Right? So we just want the first space. Okay, let's go. OK, and now you have your code, right? Isolated on this card. Okay, that's looking good. Let's go. Here. And now, how do we split this together? The item and also the size. We can still use the space, but we're just gonna be using the right most space, right at the very end. Let's go again to split column by the limiter to go for the space at the right most diameter. Right? So we're going to be spitting the item description and the size, and it's looking good in the moment. So we have here code, we have the item, right? And then we have the size. But we still have one last step. What we want to do with our sizes to add a space somewhere in the middle over here right, right between the number and the unit of measurement. So how can we do that? So first things first we need to split the number and the unit of measurement. And if we look here, is there a pattern that we could use? And if we look here, we have PK We have ML we have Rosie M l. A Cheddar and the pattern here it's our size is always to character slum So which means we can use that again. But this time we're gonna be using split Cottam by number of characters because we can split this one by the last two characters, right to get the unit of measurement. So that's taken to and as far it as possible. So which means we're just getting the last two characters off this column, which is the PK, which is the ml, right? So let's go, OK, and you have the split now between your numeric measurement and the unit of measurement. Now, how do we combine this two together so that we can just insert a space right in between them and to do that? Let's go At column. This is kind of similar to the you could think of it like a helper column that you do in Excel. Right when you create a new column and then you just come Katyn eight or Pan Different. Khatib's together. So that is what we're doing. Let's go to add Custom column. Okay, so let's just move this down and then let's just get this the name off size and then the values that we want to combine together until we have our units right? The numeric measurement listed in labels 2.2 point one. So let's just go insert. Okay, so I'll use the n percent to concoct innate or pen feels together. And then we want a space right in the middle and I'll use 10% again and combined them with the unit of Measurement, which is listed in labels 2.2 point two. So let's select that. I'll go insert. Okay, looking good, and I'll click. OK, well, it's Hold on. There's an air over here, so that naturally happened. Sometimes when we create Pacific steps right, we encounter errors. But to be able to understand it better just click on any specific error and he could see here what the like the causes so you could see her. We cannot apply operator 1% to the types number and Tex. Okay, So what it's saying is we're trying to use dan percent to combine a number or a numeric value with a text. Baggy right? Understand? Percent would only work for text values when you combine Peter toe, which means this is the part where, and I can show you really cool feature of Park Re. So we have the step that we contact in ated the two columns together, and it's resulted in an air, right? So for me to be able to fix this, which means that this column I need to change the data type. This is treated as a number of the moment. I need to change this from a number to text, right, so that when you add the compatible text and text a gator, then that would work. So the logical step for the obvious step for me actually it's just to undo the ad column and then changed type and then add the column again. But here's the Kolding with Parkway. There's no need for me to do that. What I need to do Yes, I can still keep the step over here, so if I just go step up right, it's gonna be You could see her to call them disappeared because remove wants that up. It's like we move once that backward in time before we added the color. And I can insert a new step or a new transformative step in here before we'll add the couple. So here's what I'll do. I'll change the type now two texts. And if we do that, Excel will be asking at Are you sure you want to insert step right? Because it's gonna be affecting the future steps or the steps after that. I'm perfectly fine with that. That's what I want to happen. Go insert. Right now, they're both text. So we would expect the at custom column toe work this time because we're a pending or were contaminating to text fields together. If you do that boot, you can see that it has worked without any issues because what we see here, if it's able to see that oh, we're just depending to text field together and it worked right? And you can see here that if I go here to this step and check the settings. We still have our original formula in here, and there's no need for us to redo this. That right, Because it was an error before. We just made some changes right on the step right before this. And it works perfectly fine. So this is the cool apartment park crew. And if you just go anywhere in your quarry like anywhere, somewhere in the middle and to make changes to it, right, just go to these settings and then just make your changes, and it is still apply That performed the remaining steps that you have defined ready Over here. That's really, really cool for me. Okay, so we have our clean later. It's looking good the moment. So let's go back to home. And then Oh, before we do that, let's delete the unneeded condoms. All hold shift first. Okay? We don't need this anymore. Go right click. We have removed. And then before we save right, that's changed its first to the right names. Then we have the item. Okay, Be good. Look, did it again. Okay, let's go to item. There you go. So we have our final state once we're happy with it. Let's go toe closer. Load. Then we should be able to get our team data now. 8. Transform Data - Group By in Excel Power Query: Okay, So for this example, we have this data over here sales theater. So we have your salespeople sales person over here. We have yourself region. We have the order date, and then we have the amount of sales just scroll down quickly to show today. Right? So we have a lot of off sales in for over here. And our goal is to create a grouping off the sales person and the sales region, and then get the total amount of sales right for each combination so we can use park worry too quickly. Do that. So I'll just show you on how we can use the group by functionality. So let's go to data from table. So we're gonna be opening our favorite create Editori too. Tinker with our data. So we have the entire table to just scroll down quickly just to have a quick Okay, so that's looking good. So let's just go straight to group by over here. So group by, it's gonna ask you what columns that you want to group together. Ok, so for example, at the moment, let's just go for it. Say sales person, okay? And then we have some off thesis, Ailes thinking. And then that's just type in total sales. Okay, so this is not our like ultimate cold yet, right? So I'm just showing you first. It's just used one called for now for a grouping course sales person. That's cool. OK, And then we could see here that for each sales person, we have the total amount of sales listed out immediately in here. Okay, so that's pretty easy to do it, except But right now, our ultimate goal is to use a sales person and sales region grouping. Okay, so here's what we'll do. There's no need for us to get the step. We could just go straight back to the settings off the grouping step. Okay, so that's one of the cool features a park we And then let's just add another grouping. Let's go for sales region over here. OK, so which means park, where you're smart enough to use both columns for each group and then still aggregate the sales as a total sales column. And once you do that, then you have your grouped data to stand out over here immediately, and it's also smart enough, right, because you might be thinking Hey, where's the date? Because there's a date. Columnist. Well, so park worry. So if you go back to the source that you could see here that the order date is not lifted out and the result because Park re was smart enough to determine that. Okay, this sorry group, the date is not needed anywhere, so it just deleted the date altogether. Okay, So once we're happy to data, go back to again to home clothes and look. And now we have our group data listed out here. 9. Transform Data - Unpivoting Columns in Excel Power Query: Okay, So for the transformation of your data, let's talk first about people in columns. So what we have here is just a quick illustration off on pivot right, and then pivoting the cards back. So in this left side over here, you can steed at the table we have over here. It's more for readable format for us humans because it's something that we're used to. So what you have here is just list of customers, right? And then we have the months and then the sales numbers that you have for each individual months. So which means for this one, this customer, let's say we Chip Corp right. We have February sales numbers off 14 to 4. It's fairly easy for us to read because it's just in a tabular format. But, for example, if we wanted to transform to this foreman because this is the format so you could see here individual values, right? Like the 16 to 5. So we're here. We have the Acme in January and February, cheddar until all the way to December. And we had the sales numbers well over here, and then you have the customer names repeated over and over again. Same goes for two months and this kind of format something that's very useful for processing, right. So whenever you want to create a new people table, for example, out of this one or you want to do further reports, right this form and it's a lot easier to work with because it's very useful. So, for example, if you're doing programming are doing any applications, you want to analyze the data, then this is the format that it's very useful for you to be able to proceed. So for us to be able to transform from this data format night the table for men over here into this format over here and right. The good thing is, Park Korea has tea and people comes functionality, which makes it very easy for us to do so. So let's go straight to data over here, and we have this tabular format over here right now. That's in a human readable form. So let's go through data and then let's get it from the table so that we can start working with Park. We Okay, so now we have our table loaded over here, so let's just have a quick. Look. Look good. We have the customer. We have the sales numbers from January to December. Okay, so now, ISS, how do we now transform this or pay with our columns? So if we look here right inside the transform tab, we haven't pivot columns. And if I select this, there's two options that we have. Either we can and people. Two columns are people. Other columns in our case, right? Just make sure customer is selected. And then what? We will select its people the other columns? Because we want to expand the sales numbers from January all the way to December. So if we do that right now, just like that, your table is now transformed. And you could see here that we have costumer, we have January. We have the sales number. And then we have the Monta Swell expanded now to this format. And let's just get this more like a more useful name. So let's say sales month, right? And for this one, we could just give it the amount over here. Okay, so let's just double check the tanks quickly. Yeah, this is a text looking good sales Monday's texts and then the amounts. Well, it's just shake just a currency. So now we scroll down, you concede at data is now expanded to this format. So even people didn't this one, and it's just by one click if you were to do this by hand, and that's gonna take you quite a while, which is pretty cool in Park worry. So we're happy with the data. Lets go to home clothes and load right, and we now have our new table, and the next thing that we can do so we have this one right. You can now create a new report. Let's go insert right and go for people table. This used to existing work. It's all just place this summer on the top. It's close, this one, and if we drag it, say customer, and then we have the sales amount of the columns and then we have the amount. Then it is pretty much we're recreating the original table less what we have here a while ago, right? We have January until December, and then we have the list of customers on the first column. So it is the exact same thing, and the cool thing over here is if we insert another people table Now let's go insert, sifting, worshipped if we want to create a different time off report. So, for example, if we have the customer, right, just have the amount over here. Or maybe just change it to Trudy customer, right? And all of a sudden you can now get the sales numbers, the totals for each month. And if we have this sort of table from the very beginning, it's not gonna be a straightforward in creating a people table out if it because he can now play around and let's say maybe at a layer of customers all over here and you won't be able to do that under regional data. Only when you have dissed able format over here and you can now play around and make any kind of reports out of it, and you can even recreate the original. So that's the power off on people. In columns, 10. Transform Data - Pivoting Columns in Excel Power Query: Okay, So he wants You've seen the UN pivoting video. Now what we're aiming to do is the opposite, which is pivoting columns. Right. So we have this on the right. This is our starting point now and then what we want to do in park worry is to people the column, and we would have this form on the left. So this is similar to what you're doing when you're inserting a people table. So what we want to show to you is you can do the same thing a swell inside park. So we have the months lifted over here, and now you want it to be your column headers. And that yourselves number would be included in here in a more human readable format. Okay, so let's go to the data. So we have this data overhears, right? So we have the you could think of it like an expanded format. Okay. And then if I go now to data from table that students in park worry, Okay, Okay. I don't want to worry Anti park where you could see your table being loaded over here. So what we're gonna do now is to transform right. Go to transform, and then you could see the selection over here. People caught him. And what do we want to people? Which would be our month, right? So that it will be moved to our column by column headers. So if we said at this right now, we will have a selection off the Values column. So the Values column if the one that you want to show right for the values So if we want sales so that it will be shown we have the customer on the first column, right? And then you have your months, The column headers and sales will be used to populate the rest of the ropes. And if we go to advance columns, we don't want to some the sales, right? We don't need that. So we just select, don't aggregate. If you go OK, right, you could see your table in just a single click. It's now transformed to this human readable format for you, and it's not easier to read because you have your customer. And you could just read the sales numbers from January all the way to December, right, just by describing from left to right. Okay, so once We're happy with the data. Let's go back to home and go close enough. And you have your new table all set and ready to go. 11. Transform Data - Split Columns into Other Columns in Excel Power Query: So for this example, we have this table of company. It's a company and their locations in a comma delimited format. So you could see here neither states of America and in comma and in Afghanistan. Cheragh. Right? And it's pretty hard to, for example, if you want to create a report based on the locations, it's pretty hard to do because you have for this column on the patients. You just have, like the locations, continents together and just separated by a comma. So it's not in the format that you want to do. So. Our goal here is to split this column into other columns and then transform it into a single table right off. Let's say your company and then you have your countries listed out in multiple rows, and then we also want to add a number so that we can have some numbering for each company, and you see the numbers right next to the country's. Okay, so Power Query can do off that. So let's do that quickly. Let's go to data from table. Okay, so now we have here you are all off their education, so I'll just kick control shift plus to make it bigger, babe it right and then what we want to do first. ISS with your allocations. What do we need to do? Because for us to be able to separate this into multiple columns will have to split this by the common, so we'll use Thespian column So we have your home spit column by the limiter. And then over here we have your comma. Make sure it's at each occurrence of the limiter. Go OK, and now you have your occasions now, right? You could see here, but there's quite a lot of notice over here because some of them have more locations than the others. So, for example, this one has nine locations, but the others they only have four locations, so that rest would be no. And it's not really in a format that's helpful to us because there's a lot off no values. So here's the cool thing is we just highly the company. Let's go to transform it's select un people, other condoms so that this locations over here will be transformed now into individual roles. So if we do this so we have your company, Acme over here, and then you have your locations now, right less than individual rose. And you could see here, though. There's individual space over here, so we could just select this column. So just right. Click on your column, header, go to transform. And you could just trim to do a quick cleanup and remove the spaces in front now. So if we just scroll down, it's looking good, right? We have your company. You have the locations now separated into individual rose. Now we have your numbers over here, so we just want to get the numbers right so that we have some sort of numbering for each company so it could see here acne until five. So let's highlight this column. Let's split the column by the limiter, and we'll just be using the period. So just type in custom time in the period and then just go OK, and it will now be split to the number so we don't need the locations calm anymore. So I just right click on the header, go remove, okay, and it's just called this, like your index over here, and it's just reading this to your location. Okay, so let's have a quick look at our data. It's looking good at the moment, and we just that we've already transformed this into a more useful tabular format. Lets go to home clothes and load. So from death now our database now in this tabular format and if we go insert, he can now play around with your new data. So I just said existing work Shit. It's had new people table over here and now you could just do your analysis on its A locations. And then let's say we want to count how many locations right are occurring across all of the company's and its now doable because you have transform it to this format using park free. 12. Transform Data - Filtering Rows in Excel Power Query: So for this example over here, we're going to be discussing about filtering data using Park re. So we have this table. We have our sales data table. You have your sales person and yourself region. What is the order date and then the sales numbers. So it's just do a quick school down. We have different salespeople. This it out over here, right and old of sales number lifted. Asp. Oh, Okay. So let's go back up. And our go for this one is to do a couple of fielders. First thing is, we want to feel there by Ian Peter. And then so we have this person over here, and then we want to feel there by the Europe sales region. Okay, that's our first fielder. And in our second fielder is to filter by the year 2013 or 2014. Okay, so which means we need to find a way to extract the date from our order date and then filter it by 2013 or 2014. Okay, so we want to get those two years. So let's jump straight to data from table so that we can now perform our magic and park worry So we have our table. They set out over here. So our first fielder is to do it by Ian Peter for ourselves. Person. So that's dude at that. We're here. All right, so just on select everybody and select e and Peter. Okay. So he could see over here that we have Iain Peter right now and on our crews setting strength on our applied steps. We have to step off filter growth, so I'll just go click t sex. I come over here and we can add our second condition, so I'll just go straight to advance, and then we can add thesis Ailes region filter, right. We have our em Peter value over here and then select the sales region to be Europe. And make sure it is an over here. Which means that it has to satisfy boat feel during conditions right on our table school. OK, and now you have Europe and Ian Peter for our feathered rose. Okay, so it's looking good at the moment. And now we have our second condition, which is filtering by year, traded her team or 2014. But the problem is this one over here. If you look here. It's a date time. So let's just changes quickly to just date, right? And then we have our dates. But how do we feel there just now? By the year 2013 and 2014? So to be able to do that, we need to extract date from our order date. There's a couple of ways that we could do that. But the easiest is just to go to at column, though to date. Okay, so which means based on this one, you can extract the year from this ordered a condom that it will add a new column, right? It will not delete the order date column, but it will just add a new column okay based on those values. So if we said like this one, it will just get the year from the order date and then create a new column based on that. So if you look here, some of the values we have 2013 right? If we scroll down, you also have your 2014 values, and now that's gonna be very easy for us to do our filtering because what we can do here, it's for now. I'll just select 2013 1st because we want both 2013 and 2014. The reason why I did this is let's just go to the settings icon off the filtering step because I wanted to show you the or if you go to advance, you want to use the or cross over here, then we have equals. And then we could just add the value off 2014. Okay, so what we have now over here is 2013 right? For the year or 2014. And you can even add more clauses over here if you want more filtering conditions. Right? And if you don't need this, let's just go select elite, right? That's just removed them. And we have our further and conditions. Just go. Okay. Now And what we have years now our 2013 and 2014 boat off them over here. Okay, so now we're happy with the data. We have 24 rows Now. Let's go to home. Let's go to close a load, right? And now we have your field, their data. And there's another thing that I want to show you. So, for example, you decided so it's just over her over here. If you decided that Oh, hold on. We don't need the year value over here anymore because we already have the order date. We just used the year for filtering purposes. So you just go back here again. Just right. Click on it on your query. Go edit, right. You could just go back again and add an additional steps So there's a strike kick on the year and go remove because we've already use it for filtering. And once we're happy, go back again close and load and your new data will be loaded. So it's very easy for you to modify your Cui's and then reload the actual output. 13. Transform Data - Sorting Columns in Excel Power Query: So now we're gonna be using sorting data in park Worry, and we have our starting table. Asked the data for sales. Right, For we have the sales person sells region, the order date and the sales amount. So our goal right now is to sort by a sales person and then sort by cells region. And then we're gonna be filtering our sales off at least 30,000. So we want to remove those sales values that are below 30,000. Okay, so let's go straight to park. Creates go to data from table. And one thing that's cool about the sorting off Part three is it's very easy for us to use . And what we're going to be doing now is that sort by cells person first, Let's go for ascending order. Okay, lets go for sales region and then sorted by ascending order. A swell right and you could see here There's that number one and number two over here because it tells you on what is the sorting order. It's been sorted first by sales person, right? And then if we scroll down right, we have yourselves person now sort of nice and clean. And then you have your sales region sorted? A swell asked the second level of sorting. Right. Okay, so it's pretty cool in park worry because it tells you the chronological order, Like one or two or the sequential order. Right on how the sorting supplying. So you could see here. All of our data now is nicely sorted. Okay, lets go up. Just changed the order date, right? The time we want to remove it. So changed the type to the date, right? And then we have our sales are last step is to filter the data off sales off at least 30,000. So before we do that, let's just change it to currency. And let's do a quick filter off greater than or equal to 30,000. Okay, so that's just type this year. Go. OK, so if you scroll down, all of our data now is now at least 30,000. So once we're happy with this, it's goes home, close a load. And now you have your new data nicely sorted and also filtered 14. Transform Data - Transform and Add Columns in Excel Power Query: Okay, So for this one, we're going to be discussing about transforming and adding off columns. So in this table, what we have here is a list of transactions, and then we have the invoice amount, right, The order date, and then we have the tax value s fell over here. So our goal for this one to demonstrate transformation in adding of columns is to have retrieving the first name right from column A from the sales person name over here. And then we want to get the total amount off the invoice amount and attacks value, and then we want to get the person page off tax based on the total amount. And lastly, is we want to extract the Mont and year from our order date. So we want to Additional columns added, That represents the month and the year s. Well, okay, so there's a lot off things that we want to do. But it's very easy to do in Park Creek. So let's go straight to data from table, and I'll just show you quickly on what we mean by transformation right off the column and the addition of column. So if you check the transform tab over here. The options that we have. So the options you could see over here, right if you go to the ad column quickly. ISS the selections are very similar. To be happy from tax write the from number we have from data time. And then we have some general option to smells of you looking to transform. You could see us. Well, the text column numbers dates, right, Dave, in time. So the difference between the two is transform is literally changing the column. So, for example, if you have a date you want to extract the year from the order date, What it will do is it will replace that column that you selected, and whatever operation you perform in it, it will put it on top of debt. So which means you could think of it like the order date being a race, right? And then you call him being inserted over here. The Abkhaz, um, on the other hand, is doing the same operation. The only difference is it won't remove the column that you have selected, but add a new column based on the operation you've selected. Okay, so let's go through our goals, one by want, right? So we want to get the first thing first on this first column over here, right? So we just want, like, for example, over here, we just want to get Michael. So let's go for split column. Let's go by the limiter. And then let's split it by space, okay? And then we don't need this one anymore. So let's just leave the last name right. Click on the column header select. Remove. It's just rename this. So we have now and say our first name okay. And then our next goal is to get the total amount off the invoice amount and the texts. So what I'll do is I'll just hold shift, select the two columns. Okay, so the question now is, Do we use transform Our do use that? We want to keep the original two columns over here, so we just want to use that right? So let's go to standard and then go at. And now you have a new column, which is Thea addition or some off the inveighs amount and the tax color. So let's just give this a new name and say total with tax, Okay, our next goal is to get the person page off texts. So which means based on this one, what is the percent again? Off the total. So what we'll do now is highly it again. So hold shift, Highlight this two columns, right? So the question is at the column or transform. We're going to be adding a new one because you want to have a new column on the person page . Let's go to Standard and in person off. And now you have your presentation right off the tax over here. So I'll just type in person off Tex. And just to make this better right, we have your 2.19 and there's a lot of decimal places. So we want to just have our case. Just have two decimal places over here, so this is now were transformed. We'll get handy. So while we have this column highlighted or selected, it's a goal to transform and let's have a look at our rounding right and go to round. And then he just want to keep the two decimal places go, OK? And what will happen if since we use transform right? It won't add NEW column What it will do is it will modify the current column if we go, OK, Our person taste now is 2.19 and it's just to the small places, which is pretty cool. Okay, So the next test that we want to do is to get the month and the year from our order date column. So over here, first we have the time, so we don't need that has changed a data type to date. Okay, now we want to get our month and year. So what we can do now is we won't be using transform. We'll just add a new column. Just go to add column. Let's go to date. Right. So if you look here just out of curiosity, we go to transform. If we select date, we have the same options, right? If you go back to add column, go to date. Right. So we have now we want to get the mud first, and we want to get the name of the month so it will add a new car so I could see here the month name now. Okay. And highlight this again. We want to get the year, so make sure at column is selected for your tab. Go to date, go to year and extract the year two and you cut and it's distracted so they could see the dates right side by side over here. It's pretty cool, right? So we have your month. You have a year now and we don't need the order, date and more and just go right click and remove. Okay, so let's say here, just give a name of order month. Now we have order year over here. And just like that, we have all the steps created Now, thanks to the transform or the ad column, right the functionality and park free. So if we scroll down right, we have all of our data transform to what we're after and once we're happy with it home, clothes and note. And now you have your complete data in this new table 15. From Folder - Import From Folder in Excel Power Query: So for this exercise, we're gonna be showing you on how we can use park worry to import multiple files from a folder. So we have here different excel workbooks. Over here we have Africa. We have America sales numbers have Europe sells numbers and we're gonna be importing this and then have a just a single table that contains all of the data together in park free. Because if we were to do this the old way, it's gonna be like a very cumbersome approach, because if we look at the data so let's go first to Africa, right? So we're gonna be copying this one, pasting it toe another new work shirt, for example. Right. And then we're gonna be jumping to the Americas and copying data swell and pasting it over there. And then we're gonna be jumping to the Europe and then copying the data again, right, and then pasting it to the central workbook. It's a cumbersome approach. It's a manual approach, and it's gonna be pro two areas as well. Right? Park re solves all of those issues. So what we'll do right now? So we have those tree founds. I'll just create a new workbook. OK, it's go straight to data. Let's create a new query and then it's gonna be go to from foul and it's gonna be from a folder. Okay, so already copied the folder, Pat. So this ISTEA folder, right that has those tree values. So I pasted the pad over here. Let's go. OK? And what park? Where we will do is it's gonna tell us, OK, we have those fouls over here. Okay, so that's looking good. Africa, Americas, Europe, And then a couple of extra fouls that we're gonna be removing, so go to edit. So now we have our file of this over here, so we don't need this one over here, So let's just go for let's do a quick filter. Right? So we're pretty sure that we don't want those values so we could just exclude, used just not contain the dollar sign So this tree bottom values will be excluded. Let's go, OK, and it's looking good. You only have this tree remaining. So the next fisher right now is worse from data Worse, our sales data that we saw in our spreadsheets. Okay, So to be able to do that, let's go straight to ad column, okay. And then select add Custom column. And then maybe what all do years? Let's just type in the column, name off data, and then type in Excel. That workbook, okay. And then enclosed by the square brackets, okay. And then close it by. The plant is a swell. So what we're doing here is for the workbooks, right? We're just loading the contents in here and then just go, okay? Right. Let's use this formula. And now we have the data right over here in the table and just click this one to expand it even further. So we only need the data column, select data go, OK, and then you're gonna be expanding it one more time. And this is now the actual data that we have, right? So we just live. It s it is select all the columns and load it to our park re or career right now. And I could see here. This is what we're after. This is the actual data that what we're seeing in these spreadsheets over here, right? We have sales person order, date sales, financial year, and then the Africa value or D sales region value over here. Cool. So that's looking good. So we just want the data and this one's No. So just right. Click here and then remove it. Okay? And, Dan, it's just scroll all the way to the right. And then let's go here, for example. And that's just hold shift. Right? Let's start believing the cause that we don't need. Remove that binary. This one content, we don't need this. Well, right. Click and select. Remove. Okay. And now we have Africa salesperson Order, Date. And before we do that, let's remove this well. And now we want to populate all of the values over here. Right that we have our sales region populated off this below. So what we can do here is just go to home. Let's look for the feel. There you go. Inside the transform tab to look, feel, make sure this column selected and then select, filled down. And just like that, all of the values are populated. Okay. And next step yes. We want to use the sales person order date. Right. So just go for use First Rose headers. Okay, so just select that, and the first rule now becomes your column headers. So, as a result, to spell the first Africa got included, so just double click on it and change it to sales region. Okay, now we still have some dirty data over here. We have sales person right over here. So that's just too it is. Select order. Date over here and do a quick filter to exclude our order dates. Okay, I think it's looking good at the moment, so just make sure that we have the correct data types. This one is not the Craig native. Think this is a date? Just make sure to change it and select the date. Same thing as well for the numbers, right? So make it a currency because it's the sales. And then, for example, over here just want to smell for the year, change it to a whole number, Okay? This was a swell of this. Make sure texts, and then this one a swell. This make sure s text cool. Now it's looking good. We have everything in one place and imagine it's just a couple of steps that we did know. Manimal. Copy. Pasting. So imagine if you have 100 files inside a folder, then you can perform the same steps that I did right now and have everything Load inside your table over here. And once we're happy, go to home, select clothes and low, and we're good to go. 16. From Folder - Doing Auto Cleanup in Excel Power Query: for this exercise we're going to be doing this time is doing on odor clean up. OK, so we're gonna be building on top off our previous exercise where we're still gonna be loading off the data from the folder. Right? So we have this tree sales region like spreadsheets, we're gonna be loading all of the data to a single central table, and then the additional that we wanted to split the last name and first name. So if we look at the sale state and right, so we're gonna be separating the name, okay. For the first name in the last name off the sales person and then same thing for the Americas. And then same thing. A swell for to yourselves numbers. And then at the ver end of this exercise, we're going to be having an added twists. Right? We shall be showing you another cool feature off Park re. Okay, so it's start loading and create a new workbook. Let's go straight to data. New query from foul, then from folder. So this is the path that has our tree excel workbooks. Let's go. OK, it's looking good, right? We have the street stretches in here, So let's go edit and go straight to our creed editor. Now let's make sure first, we don't have the bottom treat. So let's go here. That's filter it by the name and make sure that it doesn't contain the dollar signs. Now we have this that's retrieved the data from our workbooks. So that's at a column and a Qassam column. Just give it a descriptive name of data and type in our formula off Excelled workbook, right? And then we want to get the content that's click here and then get the data and then expand it one more time and just go okay to get all the columns inside our worksheet. Okay, that's looking good the moment. So let's just delete the other cars that we don't need. Actually, let's just delete out of them all together. Remove all columns. Okay, Remove this. Well, we don't need this anymore. Looking good, right? So let's elect this column. This is public. All of the now valued over here, so I'll just go straight to transform, feel and feel down. And then let's use the first Rose headers cool. And then just reading this to sales region, okay? And then let's remove the order dates. We're here, the Dirty Dia, and now we have our merch table. OK, so the additional step is we want to split the name by first name and last name. So that's pretty easy to do because it's just separated by the space in the middle. So let's go to split column right and then split by delivered er and we have thes space, asked the limiter. Just go. Okay, and there you have it way have that's rename just to first name and then renamed their spirit Last name Cool. And before we do that as well, let's make sure that we have all of the data type set correctly. Said this to texts. Set the ordered A to date said sales to currency and set the financial year to a number. Okay, so now we have our data cleaned up and ready to use in our people table. So we're happy with this. Go to home said that close and load. And now we have our keen data to sit down over here. Now here's the next cool thing that we're gonna do. Let's go to insert and let's create the people table out of it. Let's go. New worship. Right. Okay. Okay. Let's have some fun with our new data. So that's sayings Go for sales region. And we have the name. The first name, right that we have the sales numbers. Okay. Two had night. We're getting the sum of sales for each sales person per sales region. Okay, now, here's the added twists in park free. Let's say let's go back to our folder here and now what I'm gonna do is what if what if there are more foul, so I'll just right click here and copy. What if we have more fouls for added to our target folder? Do we need to redo the same steps that we just did in terms off pleading the data, splitting the first name and last name right, and then pending the data all together in a central table. Since we're doing our park worry based on a specific target location, here's the cool thing with park worry. He don't need to redo any single step. So what I need to do is just go back here to our work ship, right? School back here. So if you have this close a quick reminder to be able to open your courageous go data show quarries. And here's the cool thing. Here's the magical thing with Parker. Just do a refresh on this bottle over here. So what? Alouettes click, refresh and boo. You can now see your Asia data loaded A swell. Okay, so if you are unsure if I'm really doing this right, it's just double click on Asia and you could see the values over here, right? 2012 values and go back here and you could see the values are added straight up over here. Even though I didn't do a single step or tell park where you that hate, make sure to know the Asia Phile. Okay, the reason why it has happened, it's because you're Cree Waas pointed to this folder and any folder shooting you're following the same structure is theaters a swell and any found that the add to this folder will immediately be added to our were sold in table over here, and that's really cool, which means every time you have new files or you deleted files from there, just do a refresh and your park three steps will just be applied over and over again, since we have our data table here. But I can do is just go for it to the people table, right click on it and do a refresh. And now we have our Asian numbers a swell, and that's the really powerful feature of Park Re. 17. From Folder - Extract Data from Forms in Excel Power Query: Okay, This time we're going to be focusing on extracting data from forms. So whenever we try to export data from a different program, there are times spring. It shoots out the data in form format. So when we say foreign formats over here But we have here is we have six forms over here and this folder, right? So what I'll do right now? Let's just go true which form quickly, and you will see this one. It is kind of like a template, but it's consistent, right? So if you see here, we have first name. We have last name. And then we have the data from this cell if we have the marital status, age, gender and status, and then if we jump over to the next form, it say's go to form number two and you have a similar format. But the problem is your data. It's not exactly in a cool, tabular format. It's all over the place. But it's consistent, right? That's the chan ish that you need to do because if, for example, you want to create the table off all of the forms that Sanders six forms at the moment, you just need to copy paste copy and first name. Right? And then copy it to a central worksheet. Copy the last name. Happy the marital status. 80. Cheragh. But what if it's 100? Forbes Then that's going to take forever for you to do, and it's also error prone. So our goal right now is to think of a way to use park worry to get all of the data right from all of the forms over here. So let's just go over each one by one, quickly. Right. So this is another form, right? And then we want to get the first name. Last name. Pretty much all of the fields overhears a march of status, age, gender and status, and it out put it into a central life table. Sounds a bit challenging, but you're gonna be amazed with what Park word could do. Okay, so let's create a new workbook. First, let's go to data. New Cleary go to file and then from folder. OK, so we're going to be getting that folder that contains all off art forms. Okay, so I have the folder path saved here, so let's just face it in. Let's go. OK, and then wait for Park Re to start loading all of the fouls. Now we have all of the forms over here, so it's looking good. We have all the forms and then we'll just get read of Daughter one. So let's go edit and let's wait for the query editor to open. And once we have that, we're gonna do a quick clean up first on the other files that we don't need. So let's go here in name right text fielders does not contain. So let's exclude the dollar sign from it in case on its do that right now that our sign okay, it's looking good. And now we need to get the data. So let's just go to add column and custom column, right? So let's just give it a name of data, and then it's type in Excel workbook right and then content. So we want to get the content. Staff were content off the workbooks. Now let's click here on data and let's expand the column. They were just drilling and then click on it again, expanded and make sure all the columns are selected. Want to click? OK, then you will get all of the foreign data around with all of the forms. Right? If you scroll down. Yeah, it's looking good. Now we should be able to delete the columns that we don't need. So to do that quickly, let's just hold shift, right? Right. Click on it and then remove columns. We don't need that anymore. The right click on this Go remove a swell. So could see here that this is the data for form one and that we have formed to form Tree formed four, Form five and form six. Okay. Okay. So the first thing we need to do is to add any next condom. Okay, so you'll see why we're doing this in a short month. So I already click on an index card. Could see here that it just adds a numbering from zero until the very last row. Okay. And then the next thing is to create a module. Oh, right. So the reason why we're trying to do a modular here, it's because we want to number two rows and then get a consistent numbering across the different form. So what we mean by this is first things first. Let's check first. How many rows are there in the form. So if we see here form one for example, there's 11 rose, right? If we check for him to its consistent, there's also 11 rolls, right? So you could see here roll number 22. So that's 11 times two. And if we go, let's say to form number Tree. You could see here that it's 11 as well, right? Three times 11. So that gives us roll number. Turkey treat. So now, abusing the module of function over here. So let's just go here, Index right. Make sure it's hide at it, go to standard and then modular. So what is going to do it? It's gonna give you the remainder when you divide it by 11 because that's our magic number . At the moment, it's 11 rules, which for now, if I take okay, this is what would happen. Now we have our form over here in its numbered until 0 to 10 and then for a form to, for example, is also number Geo to 10. Let's delete the index. We don't need this anymore, so I'll just try, click and remove. Okay, So if you look here at the example So what we'll do for sis? Let's remove the dirty columns first. This is just now. Okay. School, Remove. And then if we go here. Right. So if we look at age, for example, age is always on row five. If you go to the next form your age, it's still again a profile. Okay, so that's the purpose of our modular, because it helps us put in fixed numbers right throughout the different forms. And there's a consistent way for us to know that. Okay, where is this data located it? No, it's in row five. So, for example, in your first name and last name, it's always in Row Zero and roll number one based on a modular, right? So which means there's now a consistent way for us to work our park for your magic so that we can extract data one by what? Okay, So first things first. The rule of Tom is we need to delete or filter out the non Rose. Okay, let's do some quick cleanup over here so I could see here, Like for Destro and destro this to Rose over here. It's all notes, right? So the number is two entries of auto to here? Yes. I'll just feel they're out to entry. Okay. Just to make our lives easier. That's check. First order. Still any non rolls over here? Yep. They're still tooth, right? Six and seven are also old notes. So let us go here. 67 Okay, now, just having another look, OK? It's looking good at the moment, right? There's no more now values. So Miss One. If we look here, there's another one. Row number nine. So let's delete nine. Swell. Okay, it's all now. Okay, now it's looking a lot better now. First things first. We're working from the form, so let's start first from the very last row and then let's see what data can we get from it ? If we look here, we can get the status. Okay, so what I'll do now it's out at the conditional column. Let's give it a name of stand Lawrence, and I'll give it a name status. And then, if the module Oh, yes, if we look here, status always appears in much of the 10 right? And then get D value from so I could see here. So it's on Data column seven. Let's go OK, you could see here are new status column. Right? Was able to get the active value. And what we'll do here is since we started from the bottom, let's just go feel it's just go right Click here. Right? Feel up. Okay. And since we already got in the values right, we could see here inactive act. They've already got in these status, we could just right click. There's no more data that we can get from this to come, so just go right click and remove columns. Right? Let's have another look. Okay, so the next road this road over here now the role number 10 right? This old No. So let's do clean up first. OK, so that's our role of Tom. Whenever we as we move along, just remove the empty columns and empty rose to make your lives easier the next one. Okay, we're gonna get the gender, so gender is always on road. Wait, So what I'll do next is it's at another conditional column. You're getting the pattern, right? It's gonna be the same steps that we're gonna be doing here. Okay, So module would be eight. It's type and gender here, then the output. So which color will we get? The value. So that's gonna be from column two. Okay. So called him to here. Cool. Right? Getting the gender now, of course. Don't forget to fill up. Okay. The next thing is, we don't need the gender female anymore, right? So we can exclude row number eight. Okay, now, what's next? If we scroll here from here, what we can get next would be boat, the age and the marital status. Okay, so it's and another call him in here, So let's go for modular. So before we do that, it's typing. H right. Modular would be five. Because that for our age is located in so called five select column. It's gonna be from column five go. OK, okay. Looking good for age. Just right. Click here. Go feel up. Next thing ISS and another conditional column. So what we're after now is Steve marital status. It's going to be on the same modular, right? So insert Margallo would be If we look at single over here, that's gonna be five. So that's five and it's selecting column. So where is it located? Its in column tree. So let's select that Here. Go. Ok, cool. We have murdered stops. Now go right click fill up. So now we have four of them. So do we still need the murders started in Asian up? We're good to go. I'll just left Click here and exclude Rose for it. Five. So now we're almost done. So we only have the first name and last name left. So before we do that, let's just removed the empty columns here. Right? Right. Click. Remove columns. Okay, so now let's get the last name. Just type in. Last name. Okay, so it's on module a one, then select column. And it's on data call up too, Right? Let's go. OK, you have her last name is go fill up again. It's looking good. We don't need a last name anymore, So let's just exclude one. And for our first name, right? We don't even need to create a conditional column anymore because it's already in here. So what are these? I'll just reading this to first name and just moved this all the way to the end. Okay, Now where? I think we're good, right? We're good with this fallen first name. We don't need to send more. Let's see module. Oye, it's done. It's job. So we can just remove it with the forming. Swept. Just go right. Click your remove Cobb's. Yes. Move the columns first to make it look better. Okay, Right. The status is just moving all the way to down. And that's pretty cool. Already have off the data listed over here. So let's say that Smarty will, son. Single female. Right? So let's just go to form six just to verify quickly. Marty Wilson, right. Single female to 50 years old. Right. So we have the data, everything extracted here. Okay. So even if you have 100 forms or 200 forms, it's just doing this steps over here, and you will be able to extract in park we with the same amount of effort. Now we're happy with this data. Goes home close and load, and you're good to go. You have your form data like the instructor data over here, and you have it in a single cool table. 18. From Workbook - Extract Multiple Criteria in Excel Power Query: we're going to be extracting data base on multiple criteria. So over here we have a sample reports whenever you extract a Portis. Well, sometimes it just appears in this kind of foreman. So what we have here is a list of Agnes ists gets here. We have the owner and then our private events allowed for this specific address and their modest a lot size in square meters and the format here, It's not exactly that, like, very friendly when it comes to creating a report out of it. Right? So our goal here is to get a less off add Assists were in the private events is allowed this? Yes. And then the lot size is between 20,000 and 32,000 square meters. And if you see this table over here, but it's not exactly a table, to be honest, because you could see here that it added the account, for example, and then you have the some and then you have another set of reports over here or addresses over here. And this extra data is being shown up again, right? And then if you scroll here, you could see that it's another headers and then you have the same format happening over and over again. So it's not gonna be very easy for us to do our filtering right based on the multiple criteria that had and then also, we need to clean up the extra rose. Swell. Okay, so there are a couple of issues that we need to solve using park worry first is we need to remove the unneeded rose and headers. So disorder needed headers right over here. And then we have the unneeded roads to swell. Okay, We need to remove. Oops. This one. We need to remove this one this well. And also, we need to replicate the owner value to Rosa. Slow to the other addresses. So for example, this one we want Satya Colas well to appear on the other roads, right? Because it's not populated in this report. And then the last step for the last issue that we need to solve iss filtering out based on the criteria that I mentioned a while ago. Okay, so what I'll do is I'll create a new workbook over here. Let's go to data. And this time you query from file and we're gonna be getting our data from a workbook. Okay, so we're gonna be getting this from this data over here. So that's actually D data that I showed you a while ago. There's gonna show you a previous votes. If you go here on the sheet, you could see her. All of the address is a private events. Right. So we have all the data here. Good. Let's go for it. Okay, Cool. So first things first. So let's go back to the issues that we want to solve. So let's do some clean ups first. We don't want the extra headers right toe the private event allowing this Just take this. Okay, so that's easy, right? That's being removed. And then the next thing is the extra rows over here. So what's the pattern between this extra gross that we want to remove? Okay. So you could see here that the private events allowed is now, and that's what we don't need. So just go here removed to know. Okay, so now the need a gross had already been removed. That's looking good. Next thing ISS this one. Okay, so we want the names to be populated down lords, and that's pretty easy. with right click here and then go fill and feel down. And you could see here that all of the names are not populated against the addresses, which is pretty cool, right? So now we have our data. It looks clean now. So which means we can now proceed with doing are filtering So are filtering right now ISS, right? We want yes, for private events allowed. So that's the first condition that we need to set Spy did. The next one is Just click your on the size and we want the filter to be between 20 Towson and turned it houses. Delicious type in 20,000 and then 30,000. And just like that, we've already created a clean report, right? Based on the data that we saw a while ago. Now we have a clean output and we've applied our filter off. Yes, and 20,000 to 30,000 square meters were good with the state up. So let's just go to home, then kills and load. And now you have your new report in this workbook 19. From Workbook - Extract Multiple Worksheets in Excel Power Query: this time we're going to be extracting data from multiple worksheets within a workbook. So for this example, you could see here We have a couple of work ships over here. The bottle. We have Africa. We have America's your Asia and then a sheet to stop. So if we look here, we have the region, right? And then we have the sales data. The sales person ordered a the sales financial year, right? And then the same form. It applies to the Americas that work shit to go here in the Europe, Right? Same thing we have DHS. Well, and then we have the extra sheet to Okay, so our goal here is to get the data from this multiple warships over here and an out Put it into a single table or a single word ship. Okay, so we're gonna do this in Park Worry again. Let's go for a new right workbook. Let's go to data. New query from foul. And we're gonna be getting this from the workbook. This is the workbook that contains the data that we saw a while ago. So it's just go import. Okay, so it's gonna Yeah. There you go. So Here's a preview. So we could just look at the worksheets one by one. Right? This is the data that mean. So let's just select the top most one so that it will load all off the work ships into our creator. Just click edit, and we're gonna be going here. And first. Experts, we definitely don't need the ship tooth. So let's just go to Nate. It's UN selection to to fill their a doubt right. And then let's expand the data from here. Just click here. And that's the columns. Right, Go. Okay. Make sure everything selected. And this is gonna be the familiar stuff for you because it's gonna be the actual contents off the worksheets. Right? So the next thing that we need to do it let's just remove the and needed columns first. It's right. Click here and go remove columns. Okay, this column over here is pretty empty, so let's just go right click again. Move. It's looking good. And let's have a look here. Okay, so the next thing we need to do is this one's looking good, right? It seems that this is the sales region that we have already. So we don't need this column any more. Remove again and this is the header column header that we need, Right? So let's just go to use first Rose headers. Now we have our column header sales person order date seals financial year except for this one. So that's just double click type and sells region. And there's still a couple of 30 data that you could see over here. We have the headers appearing over and over again because each work shit has the column header to swell. Okay, so let's make sure to remove that. Just picked this one and then let's remove the order date. And there we have it. You have a single table off all of the word shit data, and we just did it in a couple of steps. Okay, so there's no need for you to copy Paste from working to worksheet. So imagine if it's 100 merchants and they're all following the same structure and it's gonna be a cumbersome process. But with park worry, it's just amazing on what you can do. Okay, so we're happy with this data. Lets go to home clothes and load. Okay. And now we have all of the data listed out here in one table, right from the four worksheets. And now you can just play with your data. You want to go to insert a pivot table, you can create a new work shit, right Then, if you just create your reports based on your new clean data, just go to sales region and then let's go for some off sales and from your clean data from a single table, you can now create whatever reports that you need to do. 20. Joins - Intro to Joins: that's just cost the concept of joints in detail. So we have our example. Over here we have the classes listed out. We have for individual casts and enrollment, right? We have the grade, have the student i d. And then we have the student information, right for the student I d. Over here corresponds to a specific role and the students table. And when we want to join, what we're saying is, if you want to use a specific column, are condoms right from one table and then merchant with the other table. OK, so for example, in destro over here, it's night. We're saying, if we want to join stood a 91 over here, it's like we're copy pasting this one can and pasting it over here for Mickey Mouse and then for this role, for example, we have Donald up forced in the 92 right? So let's just shot to a random room. So, for example, we have Mickey Mouse again and we join it Swell to this road, and we have the data copy basin over spell. Right. So which means we have the same data over and over again for Alston I swan for Mickey Mouse and then Chandra for daughter. Student a spell. So just another administration that's just picked. Louis over here. Let's go to student eight rights to eight over here and and student eight Swell. So it's gonna be the same action that I'm gonna be doing for the rest of the rules as well . If we want to join this entire table with this table, let's fell over here. And the reason why. So let me just read this again. Just doing this for registration and the recent Fine. We have this kind of structure, right? Yes. We want to avoid the repeating data because we want we have these 13 91 over here. We just have one instance off. Mickey Maps data in this to the table, right. It's safety space from a storage standpoint, right? And at the same time, from a maintenance standpoint, it's just easy to make a change to this rover here. So, for example, if one of the students, let's say, somehow, changed her name, okay or change your surname or maybe inner nationality, right? You just need to go to this role and make a change instead of doing the same changes over and over again to this table to affect Apple and, say, 80 changes your nationality, then you just need to do this once over here. The challenge. Let's say if we want to perform in the manifests that involves want Korean Table So which means we cannot perform analyses on two separate tables. He wanted to be Emergence one right then. That's the time that we need to join them together. And when it comes to joining, there's quite a number of joints that you can perform the gate. There's different types, and we're going through that in the succeeding videos or lessons. Okay, so, for example, there's some types of joints where you want to keep all of the left records with no matches to the right table, that kind of joinus useful. For example, if you want to find that data where and it only exists over here, right, but you couldn't find the curse. Bunning student I d match over here, so it allows you to find out which wants contain invalid student ID's. Over here, it's from a glance. It's not easy to see, especially if your student table is very big and Just by doing that kind of joint, it's easy to find out. So watch out for the next lesson so that you can get more in depth examples off the different types of joints, and we can find out on which one is good for which scenarios. 21. Joins - Merging: that's tough about doing merging in park worry. So our goal here is we have this set of data, right? You can see here for the student enrollments and Argo is to get the number of classes per student and show the first name as well for that student. So, for example, you want to get how many classes, right? Has Mickey enrolled in our We have dull and rolled in a smell of cheddar. Right? And the challenging part over here is our data is structured in such a way that we have to separate tables. So I could see here, right, this table over here is the classes. A man who is the student in an waters, the grade off this specific class and student enrollment. Right? So, for example, one would be Mickey, and then Mickey is enrolled in public interaction class, and he has a great off 91. Okay? And the same goes over and over again, right for the different classes over here, and we have to student ID's. You might be wondering, why is it structured in this way to begin with? Because the good thing with this one, it's more efficient, right? because, for example, we have 12 We have eight students over here. Want to eight, right? And the good thing with student ideas being just that out over here, if you don't need to pace it in here. The student names the first name, last name, nationality did a word over and over again for each enrolment. Rightful, for example. We're here and running. You don't need to place in Mickey Mouse American. And then the data Burgess Well, over here, just have a student number and you could just referenced this. Stood a table over here and you would get the student information. And nothing is. It's also good from a maintenance standpoint because, for example, if you want to maintain your data and then let's say a person changes their nationality for sample, right? So, for example, don't death becomes Japanese and comes. It's a French or American if this nationalities lifted over and over again in this table of here that you would need to make multiple changes. But over here you just do a change for one specific cell over here and that sent OK, but the challenge right now is we want to get the people table, right? And get the number of classes. We need to somehow combined the two tables together into a single one so that we can create our people table. So to be able to do that in park were Here's what we're gonna be doing. So it's start working. First on the student enrollment table over here is to go to data, make sure to selected okay, and then go for from table range. Okay, so once we have that so we could see the entire table slow. That here. Correct, Right? It's looking good. Go too close and low. Carlson load to. And then we only want to create a connection because we're gonna be using this to merge in a short while ago case, you could see the classes connection. Right. And we have the data over here slipping. Good. Let's do the same thing. A spell over here for the students table. Let's go to data. Make sure disaffected, then select from table rage. Once we're in here, you will see the eastern A data loaded. Okay, It's looking good. And let's go to close and low, too, right? And then select only create connection Let's go, OK? Yeah, we have the students. Well, now here's word of magic will happen. Okay, let's go to data Jet data go to combine Crease and select Merge once we're in here, we want now to create a merge table based on the two tables over here. And let's select first the classes right table. And now let's select the students table. Now the question is, what are we going to be using to merge the two tables together? And the one thing in common or the one thing that links them to get her would be a student ID column. So what we'll do now is make sure that's done ideas selected in classes and make sure that stood in ideas. Vellis selected here in the students table. OK, and this is what cell will be using, right? So, for example, public interaction, it's gonna be linking now with student 91 to this rover here for Mickey Mouse and then to, for example, to Donald up, over and over again, right? It's gonna do that for everyone over here. Okay, just leave the joint kind right by default, and you could see that the selection has smashed 30 out off the first turkey roast because there's turkey rose in our classes table over here. And it means that it was able to find a corresponding match for each individual student idea over here to the student table. Let's go. OK, so you can see what's gonna happen next. So we're gonna be going here and you can see the students, right? Just a table over here at the moment. Just click here and just make sure that all the call of R selected so that we will see the actual contents. Just take this one. Because I don't want to use the column name. Ask the perfect Go. Okay. And you could see now, right? This is the grade in the class is right. The table over here and this is the student data, and it's now merge with the classes information, so I could see over here we have one. Right? And you have Mickey Mouse over here. If we scroll down, let's say to running, you could see a swell Mickey Mouse. Right? The data is also listed out over here. So it's being done on every individual rover here based on the student 90 and in just a few clicks you were able to do that. That's looking good at the moment. Tal. Now let's just make some changes. And this just a duplicate right first in the 90. So I'll just right click here, go to remove okay and change the date time or we're here. Now has the time. We just need a date. Change the date. The effort. They had a typed date over here. Okay. And then let's just rearranges to make it better at saying Aniston, I d just dragging this to the lef Mickey Mouse. Last name, Say nationality. Date apart. We're here. Cool. What? We're happy with the data. It's a lot closer load. Okay, so you could see Now it's been merged together. And even though it's repeating the student, any data? But this is exactly what we need to be able to create our people table. If we scroll there, we have all the classes and the students. Right? Stood information listed out over here acting, making jokes. It'll that Now let's go to insert. Okay, make sure your table selected over here. Let's go to people table. We can now create the new people table. Make sure that new work should second go. OK, cool. And then what we want again. It's the first name, right? And then what we want to know is how many classes for each person. And we just that. So what I did was I just dragged in the class. Name two values. Okay. And now you get the number of classes for each person's. For example, we have Mickey. All right. There's forecasts for Mickey. Louis has final classes. So, for example, they see we have two fastest. So just from this date over here were able to merge them together into one Korean table, and then we're able to perform our analysis afterwards. 22. Joins - Full Outer Join: Okay, let's discuss about the full outer joint. So for a full outer joint, what it will do is it will try to combine. So, for example, if you want to merge these two tables together, right, it will try to combine all the matches from the left side to the right side. But it will also keep the records from the left side right over here. That doesn't have a match to the right side. And the same goes his fellow. It will try to keep the right side records that don't have a match at the left side. OK, so what I did over here is this enrollment table has all current data. Except for this to Rose. Added this to rose over here with a student idea of 100 which you can clear to see over here that doesn't have a student. I d off 100 and the same espo for the student table. What I did was I just added an additional student over here. Okay, Number nine with an unknown student. But we're here. And if you scroll down on the student enrollments, you're going to see that there's no student. I d number night. So which means student number nine over here doesn't have any classes enrolled. So which means when we do at full outer join, we expect all the rows over here to have a corresponding student information to be displayed. Right? Except for this, too, because it's an invented student i D number. And that four student I d number nine. We won't be seeing any classes, right? That will be merged with this one. Okay, to further illustrate that, what? We're gonna be doing it? Let's make sure first, that both tables are added right by a park. So let's go to data that select this one. First, our classes enrollments select from table range. And once we have the data in here, it's looking good. Have the data. Now we're here. Let's go to Home House and no to. And then we just only need to create the connection. Let's go. OK, right. We have our classes connection now. Ready? Let's do the same thing. A swell for through this table. Make sure selected. Go to data from table reach. Chang, We have our students table over here looking good, right? You're the same thing. Go to home clothes and low too. And we just need to create the connection over here. What we want to do? Yes. We want to do a joint now for this to tables. And to be able to do that, let's go to data get data, combine, increase and then marriage. And this is what we're talking about. 40 full out or joined that select first table. We're here for classes, and then we have the students. Well, okay. And you need to use the student. I d to be able to drawing them together. OK, so make sure that's the night he selected for bull tables. And for the joint kind is where it all starts making sense. You could see that there's quite a number of joint and we're going to be using full out of joint. And you could see the comment over here all rose from boat, which means it will keep off the rose from the left side and the right side, regardless, if it has a match or not. Okay, so that's a like that. And you can see that the selection has smashed 30 out off the 1st 32 rows. And Kenny, because the two rows on top over here doesn't have a match to the students table. Let's go. OK, once we have our park Korea determined, though, let's just expand the students table fresh, so make sure that everything selected I'll take this one. We don't need to use the traffics right. The original column name is perfect. Go OK, and now you have the student table values on the right side over here and we have the classes right values on the left side and it's now joined together. And you could see now on the public interaction of student I D 100 right and the practice of student. Any 100 district values on the right side, our own house because there's no valid student, right River sent things to do. 9100. If I scroll away down, you could see us fell over here on student I d. Number nine doesn't have a single Vatican class, right, because there's no class or enrollment, right with student i d number nine So which means a full order joint. It's very useful for you to see which one has invalid data right, which means its existing on one side. But somehow it doesn't exist on the utter. Okay? And you could see the ones that are correct have devalues merged together so we could see that's a pranks. So tonight, six and you could see the information of Scrooge MMA about 3%. Think student I d number six and it's just it out over here. And if I just jump over to another one, you could see a swell six. So we're here in in switch a swell instead over here. So OK, so that's the use off a full outer joint. And, for example, if we removed announce Okay, so I'll just moved enough here. Okay. And then let's also remove the Dallas over here who now you could see that it looks to be now a simple emerged between the two tables and removing the invalid data. OK, and that's the use off the full our joint 23. Joins - Right Anti Join: let's discuss about the right and to join. So what we have here is a list soft data. So we have students needing counselling. So this is our goal, right? We have a less here that has classes, okay. And then the student ideas that need counseling and then their respective genders as far. Okay, so this is what we have at the moment. And then let's say we ask somebody to give us a less that still needs counseling so that we can just counter check against it. And they didn't listen to her instructions and what they gave us ISS less of students that have already underwent counseling. So that's the exact opposite of what we need. And that's pretty annoying. OK, but the good thing over here is with bright anti join. It's very easy for us to find out because for we're here, right? For example, we have CASS a. We know that the students 101 Otri 1041 of five underwent counting. So our goal now is based on this data. We need to find out which students over here from this list, right? Still needs counseling are still haven't done counting. So we're now sure that 101 Otri one or 41 The five. So this 1 101 tree. But that's not listed over here. 1041 or five have. Ready underwent counseling. So we're expecting. Just wanna one in one of two remaining. Okay, So what we're trying to do over here is we need to do a join, but this joining us different because we want to exclude all off the values, right? That already existing on this left table over here and just leave out the ones right that are unique on the right table over here, for example, You just want to leave out one a one in one or two because they're the only want left that are needing counting. So let's say let's go to class B. We have 101 or 21 of seven that already have finished, right? We don't need 100 anymore. We don't need 102 anymore, which means it's only one no one and 106 remaining, right for a class B. Okay, so the first thing that we need to do is we need to change the structure of this table first, because that's not gonna be useful for us. So here's what we'll do. Just make sure this table they selected this. Go to data from table range. We're gonna load this up in the park re editor window. Right? Okay. Make sure the class is selected what we're gonna be doing if we're gonna be on pivoting the other condoms. So let's go to transform Pivot other columns. And just like that, you could see now this tabular format, we have a list of classes now, right? And then we have the student numbers has felt listed on this column over here. Yeah, be right. We have the student id's cheddar, see? But that's looking good. You don't need this column anymore, right? So just go right, kick, move. And then it's just changed this to student. I d. Okay, just rename this So this one's looking at the moment. Lets go to home. Okay, Close and load, too. And let's just create a connection. Guess we're gonna be using this later for our joint. Okay, so we have this now, right for table one. Now for this Mona Swell. Students needing counselling. Okay. Before we do that just to make it easier. I'll just go back again here. Just right. Click and edit, right for table one. OK, so just to show you a this one, we could just rename our query and it's say, under went counseling. Okay, just say this one. Just rename this so that it's easier for us to different shape between the two tables. Now, let's go to the second table, OK? Make sure this table is selected school to data from table range, okay? And we should be loading our table Us well, over here. First things first. We don't need the gender right when it comes to joining the tables to get her anymore. So that's just right. Click here and remove and K. And then this one has felt right. There's something missing here. Yes, the classes are not populated to fill up all of denials overhears. I'll just select this column right click here, and it's cool to feel and then fill down. And now we have our classes now populated for the student ideas for a for example, for be right, and then all the way down to the rest of the test. Now Let's go to home. We're happy with this. Data. Can give this a better name. A swell. This is needs count sitting. Okay, let's go. To close a load to go to. We can only create connection and click. Ok, okay. We have bought off them. Ready? Now let's go to get data now, combine increased Merge. Okay. And it's put here. Okay, Setback underwent. Counseling first for left side. Right. So this is our table over here, and then we're gonna be using the needing counselling now. Okay, on. We just destroyed table over here days. You could see, for example, for a we have 101 1102104 Right. You have to stay over here. Now, this is where we're gonna be using he right and joint, which means we just want to keep the rose only existing in the second table, right? Or the right table. Okay, So the question now is what are we going to be using? Okay for the condoms to join them together. So to be able to do that, what we want to do ISS, we want to use the class student I d combination because each combination of this to write would uniquely identify that specific student the game because in, for example, in cast Be There's also student idea of 100. And there's also a student at 8100 and six sitting in class A said, which means we need both of them both columns right to uniquely identify a student. So to do that, it's just hold shift, okay? And select this to fund so I could see that we've selected one and two. So make sure you have selected the other columns as well in the second table in the exact same order. Okay, so just select this hold shift and said extra an I d. And you can see that it already started to try to mash them together and school. OK, and now if you have a look, let's expand this table over here and just make sure all the columns are selected. It's a thickness we don't need to use D column. Name ist traffics Go. OK, and now you have this list over here, which is still less that it's just existing on the right side, but not on the left side. OK, so we don't need this. Two columns. Ah, hold shift. Right. Click and remove columns and let's go close and load. And this is our list. Now that still needs counseling. And that's cool for class eight. Right? We have one on one and one or two, so let's double check this quickly. Okay, so Class A Okay, so we have one. A one on a tree, 104105 So which means the only people remaining in class A would be one on one and one or two because 101 04 and 105 have already finished counseling. Let's pick another casts just to verify our our assaults. Let's go for a Class E. Right? So it's saying over here is one a one and 107 needs counseling. So for Cassie, we have on a tree one of 51 of ninth. Okay, so which means if we go here, Okay, the only want remaining are one on one and one of seven, because wanna tree and one of five have already finish counter. And that's one issue. Let's have a look at class. If Okay, so we have one on 11 or 21 or tree. If we go here right, the only one that has finished counseling, it's one on one, right? Which means this tree student would still it can't. And with just that, it's very easy for us to get this result using right Auntie Joy. 24. Power Query - Convert Reports into Pivot Tables: Let's talk about converting reports into people tables using park worry. Okay, so looking have here. ISS. This is a report, right that we have just exported from a different program. And the 19 with reports issue. Yes, it is very useful to see off data, but it's usually in a format that's not very useful when it comes to translating that. Say you want to create a people table based on the data just, for example, could see here, right? If you scroll down, there's the headers. Over here, we have the not scientists for the addresses, and we have the owners a swell. But you can see here, for example, we don't have the owners populated. And then we have some sort of a count on the number of Adventists. And then there's also some off the not sizes over here. But from a tabular format perspective, we don't need this rose over here. Okay? And then you concede headers also being repeated. Okay for another set of people over here, we have current in him, Ashton, and then we have the numbers have swelled instead out over here, so there's gonna be a lot of clean up that we need to do over and over again for this set of data. Mike, Same here. Okay, so we're gonna be removing this to be able to create the people table ways on this. And it's pretty common when you have reports exported from different programs. Okay, so we're going to be discussing now on how we can clean this up using park Re and give you a nice table off data that you can use. So what we'll do now? First, ISS, that's just highlight. Okay, so I'm just holding shift and just go down here and make sure that everything is handed it . Let's go to data. It's a neck from table range over here. Okay, This one looks good. Go. OK, now we're inside the park radio editorial window and what we can do now, ISS, let's go over the province that we have, right? What do you want to clear here? So, first experts iss this one. Okay, You could see this rule over here, right? We don't need to stroll because it just specified how many adverse ists and the total off the lot size for this specific person. Okay. She conceded to get repeated over and over again. So one thing in common with them, yes, it has a null value off private events and out. So if you just scroll down, you could see that the pattern right? ISS persistent. So what we can do? Yes. Let's just go here and let's remove now. Okay. So that we can delete this Rose. Now it's gone. Okay, It looks good. Next thing ISS, we have our header correctly over here, but the header keeps on repeating if we scroll down right there's that private but allowed owner address outside. So what we're gonna be doing is weaken. Go back here and make sure to remove the private events and go. OK, Jake, it's looking good, right? We have our data, and the last that we need to do is for the olders. There's Quentin. Laugh now is over here. So what we want to do to is to populate Sachiko all the way down here and and John all the way down chakra until the very end. So we could just select this column right click the header, go to feel and in select feel down. Okay, so let's just change Date of pipes will flood size to a number. Okay. And then what? We're good. Let's go to close and load. Now we have our data over here, and it's looking great, right? So what we can do next is let's go to insert. And then we're gonna be inserting a people table here. Okay? So make sure that you work in a selective Let's go. Okay. Now what do we want to do? What we want to do is to create a people table that has, let's go for owner, right? And then we want to get the average, not size of its owner. So what? Do yourself just drag, not size two values. And it's just select this one go to value field settings on has changed its to average. Okay. And we just that with the clean data that we have were able to get the average lot size off each person and just double check, let's say let's select Carlin. Okay, we have 40,646 so I'll just go to the original data. Let's go to Carol in, right. You could see the average over here as the exact same family. Let's pick another person. Right? Let's go for Derek Over here. 10,008 05 Let's go to data. We're stared. Let's talk for him and Chelsom await. Fine. It's like this one. And he could see the averages exact same number, right? So you could use this similar technique on any report that you have exported from a different program. 25. Power Query - Modulo: Let's discuss about using modular in Park Re. So over here in the left side, what we have, it's this table. Over here, we have the region, we have the cells person, and then we have the sales numbers and you could see that it's just single or column over here and pattern that we're seeing keeps on repeating over and over again. So when I say the pattern, this is the region followed by some of person and another sales numbers. Well, they just repeats all the way to the bottom for this pattern off tree rose over here. Okay, so what we're after is our goal here is to have this tabular format based from this source data over here. So we want to transform this into something more usable. For example, if you want to create a people table to perform some analysis, we need this format right this time reform it off region, then founded by service person column. And then we have another cells called him over here. And it's pretty easy to do that in power career, using the macho to work its magic. Okay, so let's go over and make sure that this is selected for your table. Go to data and then send it from table range to bring us right into the power. Created her panel. Okay, now we have this window open. So what we're gonna be doing over here? First it let's go to add column. And then let's add on the next color. Once we had this, it's just a numerical like roll numbers over here from zero old away to 50 Tree. OK, once we have this index column, we're gonna be using the mantra of by going to add column, go to standard and then select March. And a question over here is, what number are we going to be placing in here for modular and it's gonna be treated? The reason is because if we have a look on this one, right, what is the pattern that's happening at the moment, it's re occurring. Every tree roles because see Africa Michael Jackson in the cells number. That's one group, followed by another set of tree rose Africa. John Dan. We have the sales numbers as well, right? That's another pattern of tree rose. It just repeats over and over again. And once we used tree we'll find out why. Soon. Just click, OK? And based on the index values over here what we dead? Waas? We divided which number over here by tree. And then the result is the remainder on the right side, which is this marginal collapse. They could see their remainders Over here are 012 through 12 over and over again to different and Sophie scroll downwards You're gonna be seeing on under 012 little over here . And the good thing with this one. Yes, we can now use this to correctly identify right for zero. It's gonna be the region one for it. So it's person and in 24 cells and it the same for all off the roads over here. Okay, so if we pick random one over here, we have America's for zero. Right? Cause that's the region. We have Michael Jackson over here as one OK for the sales person, and then we have to forwarded corresponding sales numbers. Perfect. Okay, what we're going to be doing next ISS, that's just select this column and then let's go to transform, right? And then we're gonna be pivoting the cut up over here and the reason we're doing that, Yes. We want now to have this in a different orientation in case of make sure that the valid column, which contains all of the values right which was discovered in the region sales person sales carb that we have that selected and once inside advance columns, we're not gonna be using the count the aggregate function. So we're here, right? You just want it to change the orientation. Okay, so don't aggregate. Make sure that selected and select. Okay, now, once we have this, OK, so you could see now for column zero, right. We have all of the regions we have for one. Over here, we have the cells, persons, and for two, we have all of the sales numbers. We don't need the index column anymore, so we could just right click here. Select remove. Okay. And then for here. Let's just reading the columns that it's easier to read. Select region. Double click your sales person. Then that's a lack. This 14 our sales numbers. Okay, that's looking good. So the next step that we need to do is there's quite a lot of miles over here, right? So what we're gonna be doing. It's delicious, too. Like this to okay and right click Go to feel fill up because we want to public all of the notes with the correct value. So now you can see here that we have this row, right? This one complete set of data It's looking good the moment this one as well And then we just want to get rid off this under rows with the null values for the region. So let's just go here and it's removed. No. And now we have our complete table. Okay? And the last step is this. Just make sure the data type is also correct. So just change this to currency. Let's go back to home, right? We're now happy with this table off data and let's elect close and loved. And just like that, you have your complete tabular data now converted from this format. The singler column format. Now into this nice table 26. Bonus Tip - Get a List of File Names: Now let's talk about getting a list of file names using par query. So what we have here is I'm inside the folder, so we have this folder or get a list of file names, right then, then we have two folders over here. You have text files, and then we have workbook. So over here we have this simple text files over here, right? The two of them and then over four, Do you work books? We have this tree X0 workbooks. Okay, so what I want to show you is how do you use par query to get all of the files like a list of all of the files inside a folder. And then after that we can do our usual magic off filtering or transforming data afterwards. So what I'll do right now is let's jump over to par query. And then over here, right inside Excel as go to Data and then go to get data. There's an option inside from file and then you can select from folder. Okay, so once we have thrown folder, you'll be able to pace in the folder path, right, that contains all of your files. And then we'll be able to have a complete list of file names very quickly. Okay, so over here we have our folder PAT, I'll just paste it in here. I can also use the Browse button to go straight to that folder. Once we're good, click OK. And then we'll have a complete list of file names. So one thing to take note off is this one, as you can see, right? We didn't specify a specific folder like the text folder or the workbooks folder. What PAR queries able to do is it's able to recursively go through each single folder. So which means regardless of how many levels of folders you have in here, Park R3 is able to explore everything inside the main folder that you have specified and then give you a complete list of files. Okay, so as you can see over here, right, we have our workbooks, three workbooks, and then we have R2 X files in here. Okay, so what I'll do is, and that's go to transform data. And then we can just play around and see what we want to do. For example, let's say in this list, we just want to keep all of the text files. Okay? So let's say text files are pretty much scattered across multiple folders. And then we just want to have a clean list of text files. So that's very easy to do. What we'll, we'll do right now is over here inside the extension column, we can just do a quick filter off just text files, right? So if I click OK, then we have just text files filtered out over here. And now, since we just want a list of file names, mono do is, I'll just right-click on the name column. Okay, assuming this is the one that you want to keep, right? And then we use a select, Remove, utter columns. Once we're good, right? We just have this two files over here and then I'll just go too close and load. And here is our filtered lists off data, right? So let's just jump back over here just to have a quick test if this is working fine. So what I'll do, right? Let's say I went to workbooks, okay. And then I also created the NADH are lets say source text files. Okay, I'll just make up, okay. And then this just add some more. So let's say New and then let it go to texts, right? And then let's say this is my text file. Okay, so once we have this over here, so we know that we created a new folder, right? And then we added a text file and psi, right? And then since we created this query that targets txt file is what I'll do is let's just go to data, refresh l, And then let's see what's going to happen. And now our query has finished running, right, three rows loaded. So it was able to recursively check all of the folders and then it got our new text file as well. Okay. 27. Bonus Tip - Get File Names Based on User Selection: Now let's discuss on how to use par query to get filenames based on a user selection. Ok, so over here inside this folder, we have this three folders, right? Documents, we have sales, and then we have text files inside documents, just pretty much blank documents over here, Office document, that's right. And then this jump over to sales. So for sales we have this sales spreadsheets, Excel files over here. And then for the text files, pretty much just common text files. So what do we want to do is present a dropped down to the user. And then once the user selects one of the folder names, then park query would be able to retrieve the files inside that selected folder. Okay, so which means if you want to create a query that would be dynamic or debt would change depending on which folder, right, the user has selected. Okay, so let's jump over to Power Query. So first things first, we need to create a query that would be able to get all of the folder names, okay, the list of folders. So pretty much we saw three folders over here, right? And that's jump over here I will go and we need this tree folders, okay, to be returned inside the park R3. Okay, so let's go over to data. Alright, get data. And then from file, we'll just get everything from the folder. Okay, so what I'll do is while it's loading, I'll just copy this one quickly. And then we can just paste in the folder Pet. Okay, here we go. So this d folder, and once we click ok. Then we should be able to get a list of all of the files. But the next question is, how do we get the folder names? Okay, so that should be pretty quick. So I'll just click transformed data and then that's perform our magic over here, right? So what we have right now is a list of all of the files inside this, like all of the folders. Okay. So you can see, right, the Office documents, the spreadsheets, right. And then the two text files. So what I'll do is let's just scroll all the way to the right. Okay? So what we can see over here is d folder path. So this is the one that we need. So what I'll do is just remove the other columns, okay? And it is what we need right now, right? We have the documents, we have the sales folder and the text files, but what we need is just unique values over here. So what I'll do is I'll just right-click again and you can just remove the duplicates. So we only have this tree folders, documents, sale of San text files. So it's up to you. So for example, if we want our dropdown value to have the entire folder path, right, then you can keep it this way. But for example, if you just want the documents or the sales or the textFile is just the folder name, then you can do a quick replace to just remove the pat in front. Okay? But for example, will just keep the entire folder pet, so we're happy with this one. And then for this one, right, for the query settings, and that's just change it with a different name to just make it more descriptive. So it's good practice to name your query. Name over here, right? So that we can be able to differentiate from one query to an under quickly. Okay, so, and say this is Lynas folder names. Ok, so I'll just click Close and load over here. Ok, so after this, we should be getting our less a folder names. So now that we have this list of folder names, right, let's jump over to the other shit and then we'll be creating a drop-down over here. So let's say is going to be our selection or the drop-down, right? So let's say folder. And then let's create a drop-down over here, right? Let's go straight to data. And we have under Data Tools, there's the data validation option. So once we're inside data validation, we're going to be creating a drop-down value. So let's just select over here a list, right? And then our source, so our source will be this lift off folder names. Ok, so once we have the selected, right, so that's just expand this a bit so that you can see the entire folder path. And we have our documents, we have our sales and text files. Okay, so we can just make a selection over here, right? And then our next step is to retrieve the files based on disk selection value. Okay, so that's our next problem right now. So what I'll do is let's just create a new query that's just based on this value. Ok, so what don't do this data, right? And let's select from table range. Okay, so we're good to this one. It will transform it into a table. Ok. And then once we're inside this one, we just want that specific value. And I'm going to show you why we're doing this in a short while. So we have this value, okay, so nothing fancy. And then I'll just right-click on this one and then we want to drill down so that it gets treated as just one single value, okay? So it's not a table anymore, but just that specific value, that dropdown value. So once we're inside over here, same thing. Let's give this a more appropriate name. So let's say this is the folder name. For example, digits are selected folder name. Okay, so once we're good with this one, I'll just go too close and low, right? Because we don't need this on the spreadsheet, so I'll just select only create connection, okay, so it's not going to get loaded. But our query is over here. And if you're not x, right, this is a query that returns a table of values. But over here, since we drill down to that specific value, right, you can see this as a specific text value. So it's just a singular text value we will be using on another query. Okay, so the question right now is how do we write a query that would get the list of files, right? So pretty much the same as what we did a while ago, but we are going to be making some tricks with afterwards. So let's go to data, let's select get data from file and then from folder. So pretty much the same folder from awhile ago. Okay, so what we will do right now is let's jump straight over here, right? 40 folder Pat. Okay. So same folder. If you noticed, we're doing the same steps S a while ago. Okay, so we're good with this one that's GoTo transformed data. So we have all of the files listed. Inside that folder. Okay, so what do we need to keep? Right over here is just the list of file names. So what I'll do is I'll just select name and then I'll just right-click and then select, Remove other columns. Ok, so now we have all of the files, right? So we're good with this one. Okay, so I'll just select close and load T2 so that we can see this quickly. I'll just select existing worksheet. And then we can just place all of our results over here. So I've selected that click OK, and then we have our list of files. But one of the main questions right now, yes. Okay. That looks good. But although we haven't been doing are filtering, right, based on the drop down selection. Because over here with, for example, we selected sales, but we're not filtering desk to the sales folder, okay? So we're missing ones that we need to connect this two queries together. Ok? So this query, right, and this folder, an inquiry, we need to somehow pass the value of the folder name into this specific query so that it will be able to tie that together. Okay, so what I'll do right now is that school back here, okay. And I missed one important best-practice, right? I didn't rename this query, so let's do that right now. Okay, so remember to rename your query at all times. And then let's say this is again the file names, right? Based on folder. Okay. But the question right now is it's not basing editing on the folder, right? Because it's just the main folder and then getting everything inside that mean folder. So what we'll do right now is let's have a look over here. Alright, let's go back to source. If I check source, you can see on this bar over here, right? This is d code or the actual language that Parker uses to be able to do or work its magic. Okay, so if I expand this right now, right? You can see over here Folder dot files. And then we have the folder path over here, which is just the main folder right at the top. But what we want to do is get it from the outer query that we just created, right? If I open this one for the queries, it's the folder name query, which is the one that contains the drop-down value that we have selected. So it's as simple as changing this string over here, right? I'll hold Shift. Okay, I'll just hold this one and then we can just delete this one all together. So once we delete this, the question is, what do we pass in year four Park query to do its work? It's going to be folder name, which is exactly this query that contains the drop-down value. Okay, so I hope that's making sense right, on how we're tying things together. But after desk, right, we're expecting it should be working, are up and running, but there's going to be one minor issue. So what I'll do is let's just saved S, right, let's just go over here and we have an error. Ok, so in saying that it may not directly access a data source, because one of the things when it comes to park query is by default, different queries won't be able to access one another, okay? But what we can do right now is we can just change the setting so that we can have the worries, interact, or exchange information with one another. Ok, so what we'll do right now is let's just go to File, okay? And this go to options and settings. And then there's an option called query options. Once we're in here, let's just go straight to privacy and then we'll be able to change or allow queries to work with one another. So let's jump over here, right, to privacy. And then under privacy levels, and that's go to ignore the priority levels and potentially improved performance. So over here, it's not exactly that obvious, right? But this is the one that also allows us to exchange information between queries. So I'll select this. Okay? Alright, and then let's have a look over here. Okay, I'll just click on others, right? And it's now working, right? If I just jump over to different steps, you can see that it's working now. Since we have the sales folded or selected, if you notice, right over here, we only have the cells worksheets now being lifted out till, which means it's now working and is now able to filter. So you can see we just have the filenames for to sell spreadsheets. So we're happy with this. Let's go to close and load, right? Were able to change our query. And now we have this spreadsheets over, listed out over here. Okay, so that's really cool. So let's say we change this right now. Let's test it out, right? Because technically we're done, right. So let's just test it out to, let's say documents. Okay? So just one caveat is because queries don't execute by themselves automatically, right? Okay, so what we will do is there's just an added step off refresh, all right, or you could just refresh this specific query since this is the only one that gets the file names based on the Folders section. So let's click this and we have the Office documents listed out. So let's change this again and let's try it out to the text files, okay, textFile selection. Let's refresh this. Okay? And then let's see what's going to happen. And we have all of the text files inside that folder listed out. Okay, so that's pretty much for park. Being able to fetch values the inadequately. Okay, so I hope this gives you an idea if you want to base it on the dropdown value or obesity on a specific field. And then you should be able to use this technique to be able to make your queries dynamic. Okay? So one thing is also if you want to be able to, let's say, dynamically update the query. Let's say base on the selection change of the dropdown. Then you can use a macro S fell to have this added to that. 28. Bonus Tip - Add Column from Examples: Now let's talk about adding column from examples using Parkway. So for example, if we want to transform our data in a predictive way, right? There's a pattern. So for example, something as simple as changing the names over here to uppercase, instead of using the par query functions, right? The functionalities inside the minumum, you can type the resulting texts, right, or the transformation. And then par query would be able to infer on what you are trying to do, okay, sounds like magic, but I'll show you quickly on what we can do with this option inside part query. So what we have over here is our data off, like names of the owners over here, right? And then we have the address of the location of the event's location. And then if private events are allowed or not, and then we have the lot size in terms of square meters. Ok, so what we'll do right now is let's just jump over to park free. Let's go to Data and then from table range. So that's just select this one. And then let's select from the table. And once the data has loaded, okay, and what we're gonna do next is let's just go straight to this option of add column and then column from examples. So in our case, let's start off with something simple. Let's say we want to change our owner name, right? And that's a Sachiko over here into all taps. Okay, so let's have this column selected because we want Parkway to just base the transformation often to values off this column. So add column, column from examples and then from seduction. Ok, so once we have this, right, so what we're going to do is we can just type in a sample result from one of these values. And then let's see if park where we would be able to infer that. Ok, what we're trying to do is to have everything in caps law or in uppercase. So I'll press enter. Okay, so you can see over here, right, part query was able to infer and you could see all of the names right now just magically transformed into the uppercase. But to be able to understand better on what Park, where is doing behind the scenes, right? If you check this one over here, you add column from examples. We have text dot and then the owner column. So what Park where he is doing is it's also showing you on what it's trying to do so that you can understand better, okay, this is what I want and if this satisfies your expectation, and so you could just click OK, right, and then now you have your new column. Okay? So there was no need for me to go through over here, right? And then find the specific functions or specific functionality that I need to be able to achieve my result. So if you're doing something that's going to be predictable, right? That's going to be a pattern that you're going to be doing across the values over and over again. Then you could explore column from examples as a quick way. To accomplish this for you, okay? So what I'll do right now is we're good with the uppercase. I'll just right click on this right and then remove that column. Okay, so let's think of other examples that we could try out. So for example, let's say we want to change this one, the owner, into an email address for example. So what we'll do is same thing, right? Column from examples, from selection. And then let's say we just want to add the name, right? And then at gmail.com for example. Okay, so Sachiko, right. And then it's a gmail.com. So what we're doing is pretty much good things, OK, two things over here. We're changing the owner name right into small letters, okay? Lowercase. And then the second thing is we're adding or appending, right? The at gmail.com at the very end, right, for a desk texts. So let's see what's going to happen now. Press enter. Okay. So he was able to infer the swell, right? So we're doing two transformations, right? But par query is smart enough to know what we're trying to do. Because if you just notice right otters over here, we have eileen at gmail.com. He was able to transform it correctly. And if we check the actual function over here, say good, see you over here, right? It's trying to combine two pieces of texts or get or we she's the lowercase of owner. And then we have the at gmail.com. So that's really, really cool. Okay. But it's far from perfect, right? There are times where it's unable to infer it based on your examples. So if that happens, if it doesn't work, what do you expect it to do? You can add more examples over here so that you give more opportunities or more examples for park query to work with. But for this one, we're pretty much happy. Okay, so I'll click OK, and then we have our new column, right? It seems like magic, right? But it must able to infer it on what we're trying to do. Now, let's try with another example. Lets remove this one again, this column. Okay, we're good with this. And then what we'll do is let's say we want to combine two values together. We want to combine the owner value and then the lot size. Okay, so what I'll do is let's go to column from examples. Let's add a column again. And then instead of from selection, since we're involving both the owner and the lot size columns, right? I'll just select from all columns. Ok. So which means what we want Park we're going to do is based on what we're doing, okay, consider all the columns and then see or try to infer on what we're trying to accomplish. Okay, so let's say I'll just play around with this. That's say Sachiko. Okay. I'll just type in the name, right? Sachiko and then let's say has a large size off 21x 5p, 60 square meters. Ok, let's see if it's able to infer. Now we have the name right. It's actually closer. That's sign it off. 2.2.3, 8-0, right square meters, so you're able to infer. So let's just jump in and do a quick spot check on one of the values. So let's say this one. So we have Mickey has a lot size of 6 thousand square meters. Let's check the function. So it's trying to combine this values together. We have the owner name, right? That's correct. We have D Hess and lot size off. Yep. We have the text from this specific column over here, right, the lot size. And then we have square meters. So that's really, really cool. It was able to change it into what we need and it's go to okay. And it pretty much we have what we can do with the ad come from examples. 29. Bonus Tip - Group and Rank Items: Now let's talk about grouping and ranking items inside part query. So one of the useful questions that I get s for this specific scenario, we have a list of owners over here, right? And then they have different lots that they own. And over here, you could see the lot sizes for the places, okay? And then the goal is what if we want to get the top to lot sizes for each specific older, okay, so which means for Sachiko, we want to get the top two from this group over here. And then for Joanne for example, and we just want to get neat up to okay, from the biggest to the smallest size. Okay? So it's kind of tricky if you tried to think about it in par query because it's not that straightforward on how you can create this, but I'll show you on how to use groups and then you'll be surprised on how imagine it there the solution would be. Okay, so keep an eye on this and then let's go straight to par query by going to data. And then it's just select anywhere in the table and then pick from table or range. Ok. So once we have park where you loaded up, okay, so keep in mind you want to get the top two per owner, owner group over here. So to be able to do this, we need to have this sorted correctly first for this table. So what we're gonna do is 40 owner column less chest sorted by ascending order. Ok. So we have Ashton and the top right and then carried in and a followed at the very end by such a goal. And then after death, since we want it to be the top two from the biggest to the smallest lot size. We'll be sorting this column as well for the lot size. All right? And let's select sort descending, okay, so it's going to be the largest, Right? And then followed by all the way to the smallest. So for example, let's say for car lean over here, right? You can see that it's now sorted from the biggest to the smallest, okay, so pretty much the same for each owner group that we have over here. So the next question right now is how do we get just the top two? For example, for Sachiko, we only want this two rows over here, right? You want this two values to just remain in this table. Okay? So we, since we have this sorting setup, so we'll be doing the grouping next. So what we're going to be by using the owner column because we want each group, right? Let's say card in would be one group, Derek would be one group, island would be one group. And then we'll take the top two values for each individual group. Ok, so what I'll do is I'll just right-click on this column. And then there's going to be the option for groupBy. And then with this groupBy over here, there's just a couple of settings that we need to do, right? So this is the column owner, okay? We're good. Operation would be all of the rows. We want it to be considered for all rows for column name. That's not important, so we'll just click OK. Once we have this right now it's cleanly organized to each. Owner name. If you select, let's say this specific table over here under Ashton, you could see that it's all of the entries under ashmem is just one entry. But let's say let's go to Sachiko. We saw a couple it a while ago. So you can see it's now this specific group, which is really cool. Okay? So which means we have everything sorted out to the specific group we're in. It's like your group name is the owner name. Okay. So let's say as checkout, Derek and maybe he could see d four rows as what we saw while ago. Okay, now the next step is to add a custom column, which is going to be an index column that would just number all of this robot over here, right? It's like 1234. It's like ranking the rows from the top to the bottom. Ok, so that we can fill this in our next step. Okay, so what we'll do right now is let's just go to Add Column and then let's select custom column over here. And we will be doing is let's just type this specific function table. Okay? And then let's add an index column, index column, right? And then what we will be placing in here, right, is the new column name that we have over here, right? And then we just give it a name off. Let's say this is a rank column, okay? And then followed by what is the starting number? And then what if the increments, so which means one? And then since it's going to be plus one, it's going to be two and then 3456, etcetera. Ok, so this is our index column and then no syntax error. So we're good to go. Let's click OK and then let's see what's going to happen afterwards. Now, let's have a look at patch them. So ashton, it's pretty straight forward. You can see our new rank column, right? There's one because it's just one rule. Now, if we go to Sachiko and then click on this, you could see that there's a rank column and it's pretty cool. It's now numbered from one to six and it was able to follow a swell the lot sizes since we started that in our previous step. So what the custom column dead, right? Yes, it added the index column to every individual group. Okay. It's like magic, but it works great, right? If we go to Carolyn, you could see the rank economists, well, 123 over here. And then for example, you could see four over here as well. Okay? So since we have this rank right now, it's very easy now to perform our next step. I'll filtering just the top two of each group. So what I'll do right now is let me just right-click on this new costume column over here, right, this table. And then I'll just remove the outer columns because we don't need those anymore. And then after that, we could just expand this, right? I'll just click on this and then I'll just untick the US original column name is prefixed. We just want to have the column names ask themselves in the expanded table. Now, if we expand this, right, and we have everything over here, it's pretty much the same table for the first four columns as what we had awhile ago. But the rank right now is over here, right? You can see for Ashton, we have just rank one. Okay. Carried him would have 123 because there are three rows under her name. And then we have narrowed it for, okay, if we jump over to Sachiko, for example, we have six rolls and they are now ranked from highest to lowest. Ok, so that's really cool. And the next step is let's just filter it out. We just want the top to write just the top two solid. Just select 12 over here. If I click OK. And there you have it. The good thing with this approach is it works as well for this, we want to, for example, let's say for Ashton, she only has one rule again, but this approach still works fine. Okay? And once we have this, since we don't need the rank, we only need the data that gets filtered out. We can just remove this column altogether. Okay, now we're good. Let's go to home and then this close and load. And now you have your groupings and the top two for each group. And there you have it. You can use this technique when it comes to application of ranking items within a group. 30. Bonus Tip - Joins Data Setup: Okay, now let's talk about the different types of joints in par query. One of the things that it could be confusing at first is the different types of joints because the names off the join types, the sound foreign and it's not that intuitive at first glance. But what I'll do is I'll explain it's step-by-step so that whenever you see like a specific scenario, you can immediately select on which join would be perfect for you inside par query. What I would suggest is to watch all of the joint tutorials that I'll be putting out because I want you to see the big picture, right? If you know all of the joints than you would gain an appreciation on which join would be perfect for which scenario. So at least you have all of them. And they're your toolkit, right? And you'll be able to use them depending on your scenario. Okay, so what do we have here right now for the dataset up is we have a list of tenants, OK. And over here we have a less of owner, so forth, tens. It's pretty simple, right? We have the names of the tens. You have the room numbers on the word dares thing inside. And then we have the start date when they started renting out or living inside. Okay. And then we have the owners as well. We have the owner of the rooms and then we have a list, that room number, and then have you paid the fees. Okay. So over here, if you notice, there's pretty much three types of people. Okay. If we were to categorize them, it's not just tenants, that's not just owners, but they're pretty much an intersection between the two tables. So for example, let's say let's go to Mickey, okay. He could see that Mickey is both a tenant and an owner as well. Okay. So there's that type or category station, right? That it's existing in both tables, tenant and owner. And then there's also, let's say for Daisy, who's only a tenant, but she's not an owner. Okay. So which means that's number two, tenant only, but not an owner. And a number of tree would be, let's say four chip over here. Chip is an owner. Okay. But he is not a tenant. Okay. So keep this tree categories in mind because it's going to be useful in demonstrating on what are the differences between the joints. So there's the tenant and an owner at the same time. There's a tenant, but he or she is not an owner. And then vice versa, if we have an owner that is not a tenant, okay. So keep this tree and then move on so that we can explore on the different types of joints. 31. Bonus Tip - Full Outer Join: Now let's talk about the first drawing, which is the full outer join. So a full outer join pretty much joins everything together, okay, all possible combinations. And I'll show that to you quickly and it gives you a quick snapshot of everybody, right? You have tenants on the left side and then you have owners on the right side. And you can quickly see the three types of people that we have mentioned awhile ago. Okay. So recall the tree types would be the owner. Okay. But owner not attendant and him have a tenant but not an owner. And we have both a tenant and an owner at the same time, would have full outer join. You can have that view very quickly to see all of them inside one table. Okay, so what I'll do right now is just go to Data and then let's get the data from the tenants. Okay. That's just quickly set us up for all the far succeeding joints. Right. And then let's just save this as a tenants table. Okay? So this is tenants over here, right? This ship is close and load. Okay? And then let's just set this essay connection because we don't really need this shown in a new sheet. Okay, so let's just select only create connection and we're good to go. And then let's do the same thing for owners as well. So I'll just go here data and then from table or range. And then let's do the same thing and rename the query. Now, let's give this a descriptive name of owners. Once we're good to go, I'll just go too close and load. And then same thing for only create a connection. Okay, so that's looking good. Let's go to the fun part which is merging. Let's go to get data, and then let's go to combine queries and select Merge. Now once we're inside the merge window, there's gonna be a couple of selections for the joints and we'll be discussing this one by one. So what I'll do right now is let's just keep it consistent on the first table and that's select tenants. Okay. And then on the second table and that's select owners. And over here we have all of the types of joints. And let's just focus first on the full outer join over here. Okay, so the question right now is, what is the column that Merkel going to be using to combine the two tables together. Okay, so the one that links them together would be the name. Okay, let's use that because that's the unique, okay, in this scenario is unique identifier for each individual row, okay, that's used to name right over here. And again, but best-practice, there should be a unique ID, okay, that differentiates them from one another. But in our case, it's going to be the name. Okay, so let's have a look on what's going to happen if it's a full outer join. So I'll click OK. And then once we have a good look over here, we can see what's going to happen. So we have all of the 1000 columns over here, right? And then we have the owners, but it's still inside of table. So what I'll do is I'll just quickly expand this, alright, and then I'll just remove the US original name us prefix. Actually, let's keep that so that you can quickly differentiate which is which. Ok. So let's click OK. And it gets expanded. What I'll do if that's just close and load and analyze what has just happened. Okay, so we have the owner columns over here, right? And then we have the tandem columns. And what you see is you are now able to join, alright, pretty much the tenon, right? You can see who is a tenant and an owner at the same time, right? And then you can also quickly see on who is not a tenant, because this is black, this art of ten of columns. And then Minnie is an owner, right? So you can see that second type, but you can also see on who is a tenant, MET not an owner. Okay. So pretty much it's going to be the c q0 and Donald, that's this third type. Okay. So the full outer join, pretty much joints, everything. Okay. If there's a match, it's going to show you that there's a match for that grow, right? But if, for example, on the first table or what we call it as the left side, if it doesn't exist, right, it still shows you the second table are derived values, okay? But if the left table access right, or the first table exists, then the second table doesn't access. It still gets shown over here. So it's pretty much a big picture. Joints everything over here so that you can quickly filters. So for example, let's say I just want to see as a two blanks with the tenants and owners existing over here. So we quickly have a look on who are the owners, but not a tenant. But you can do the same thing as well, right over here. And then let's say I just want to keep the blanks over here, right. So if we keep the blanks, then you can see who our tenants must not owners. Ok, so the full outer join is very quick for this to have the big picture in one shot. 32. Bonus Tip - Left Outer Join: Now let's talk about the left outer join. So sometimes this naming of the joints, it can get confusing because when we see left, okay, when it comes to the merge window, left is like the first table, okay? So just to get it out so that we can have a better or a quicker understanding on the different types of joints. So when we say left, you could think of it and say this is the first table. So this is what we're pertaining to ASD left table over here, okay? And then the second table would be pretty much the right table. So which means for the left outer join, if this is the first table that we're using, then this will be our focus. Ok, so for the left outer join, what it will do is it will look at all possible combinations that are existing on the left or the first table that we have placed. In other words, let's say if we want to join this table with this one using a left outer join, it will just look for like this values, right? This is going to be the main basis. And then it will just look for matches on D right side or the owners table. And if it doesn't exist, then tenants would still be kept. To better understand that, I'll just quickly do the merge for you. Let's go to data. Since we have detachment and owners connection ready, it's going to be easy to merge them together and this go to get data, combine queries and then let's do the merge. So let's select the left outer join and then see what's gonna happen next. So let's say let's select tenants as our left or first table. And then let's select owners as our right or second table. And What's going to tie them together would be the name. Okay, so now we're going to be selecting left outer over here so you could see all from first and then matching from second. Okay, so that we can understand this better. I'll just run this quickly and then let's expand the owners table over here S what we did in the full outer join. Okay, so that's just expand this quickly. Let's load this up and let's analyze on what just happened. So you can see right, pretty much for the tennis table. If we go back here, for the tennis table, there's pretty much six values. If you notice on our resulting table, you can see the six values as well over here because it's a left outer join. So we're keeping the left table in tech. Okay? And then what we're seeing on the right side or the second table are pretty much the matches and the non matches for the owners table. You could see that Mickey is boat, right? A tenant and owner at the same time. And then we have DC, who's a tenant but not an owner. Ok. So what it did was it kept the left table intact. And then from the right table, the second table is just trying to match everything based on the criteria that you have set. Okay? So that's pretty much for the left outer join. 33. Bonus Tip - Right Outer Join: Now let's talk about the right outer join. If you were able to understand the left outer join, right outer join is very, very similar to the left outer join. The only difference is instead of the left table, let's say in this example would be the main table. Instead of the left, it's going to be the right table, okay, so which means the right table that we will be selecting will be intact. And then it will just try to match everything from the left table. Okay, so that's pretty much 4D, right? Outer join. So let's just do this quick merging. Then we can have a look on how it looks like. Let's go to data, get data, combine queries, and then let's do the merge. Okay, so let's do the same drill. Let's just set the tables up quickly. Let's say four tenants on the left, owners on the right. And let's just set up the name. And for D type of joint that select right outer join. And you can see that from the second and then matching from the first, which means we want to keep this second table intact. We noticed awhile ago and right, for the owners, there are seven values as compared to the sixth values off tenants. So we're good to go. Let's go OK. And NSC, what's going to happen next? Okay, so before we start, let's just expand this table quickly so that we can have a look, click old and this go to home, close and load. Now let's have a look at the values. So if you notice we have the seven owner values over here, right? Because we used the right outer join, which means we want to keep the entire table intact and then try to match all, like show all of the possible matches from the left table. So you can see that Mickey over here, excess. Okay, so you could see that There's pretty much two types of people that are shown here, right? Both an owner and tenant at the same time like Mickey over here, right? And then the second type would be who is an owner but not a tenon, like mini over here, right? Because what we're doing is keeping the right table in tech and then getting all of the possible matches from the left table. Ok, so they're very much the same hand-in-hand for the left outer join and the right outer join. Okay. It's just more off which table would be kept intact and then would be matched to the outer one, okay. 34. Bonus Tip - Inner Join: Now let's talk about inner joins. Okay, so for inner joins, it's gotta be involving both tables. Again, the only difference is that for disjoin, you want to check on which values exist on both table. Okay? So in other words, what we're looking for over here is who are the ones that are tenants and owners at the same time. Okay, so that's the inner join. So let's just go over this quickly. We have data, let's go to get data, and then that's combined and merge. So let's set up the tables quickly. We have tenants on the left side, right, and then owners on the right side. And then this go for the inner join. And then let's select name. Ok. Let's click OK, and then let's just expand the columns quickly. Okay, let's go to owners over here, right? Looks good. Let's go to home, close and load. And then what you see over here is pretty much all of the ones that exist in both tables. Okay, so we have Mickey and goofy and Pluto that are both owners and tenants at the same time. 35. Bonus Tip - Left Anti Join: Now let's talk about the left and T joints. So for the left-hand to join, what it will do is we are looking at what only access on the first table. And it should not exist on the right table. So which means, in our case, what we're looking for when we're using the left anti join. So assuming this is the left table and this is the right table, right. We just want to get the ones that are tenants, but are not owners. Okay. So we're just looking for one specific time. Okay, so let's go over here. Let's go to data, get data. And then it's going to combine queries and merge. And then that's just merge this two getter and as go for ten amps on the left side. And then we have owners on the right side. We're going to be joining via the name column. And then let's select the left and T joint. Okay, that's it. Go okay, over here. And then let's have a quick look on what we will be getting. Okay, so once we have this that's just expand the owners table. We're good to go over here and then this goes to close and load. Okay, so what we have as a result with the left anti joint is kinda like a join, but an anti effect, right? Because we just want it to take place on one side and make sure that on the right side, on the other side, they're all black. Okay. So let me have is this list off tenants, right? And they're all tenants, but they are not owners. Ok. So I'm pretty sure once we go to the right and to join tutorial, you will have a guess on what it will look like. 36. Bonus Tip - Right Anti Join: Now let's talk about the right and to join. So it's pretty much the opposite of the left and T joint in terms of direction for this specific setup. Assuming this is our left table for the Thames. And then assuming this is our roundtable 4D owners, if we use the right anti join, we will get what is only existing on the second table, but has no overlap on the first table. In other words, we just want to get the list of owners, right, but has no intersection with the tenants table. So which means just an owner and not a tenant. Okay. We want this specific list four, the right. Anti joint. Okay. So let's go over death quickly. That's go to data, get data, and then that's combined queries and select Merge. Now we will just select the two tables quickly and then have d, right, and T joint selected. So we have tenants and owners. Ok, let's use the name column and then let's use D, right? And to join. Okay, let's go OK, and then expand this quickly and then we can have a look on how this will play out. Ok, so we have the owners table. Let's just expand this quickly and then go to home, close and load. And then we have our resulting table. So with the right anti joint, what we have is pretty much all of the owners listed out over here. And then we have the TNM values as blank, which means we have retrieve all of the owners but are not tenant. We are just targeting on this specific type. Okay. So as we went through all of the different types of joins in part query, I hope this has been helpful so that you can quickly select when it comes to joining depending on your needs. 37. Thank you and Congratulations!: Thank you and congratulations on completing this course. So I hope that this has helped you learn Microsoft Excel part three, basic. So you're also able to leave a review on this course simply by clicking on this link over here, this reviews link. And you would have the leave Review button enabled as well. Simply click on that button and you're able to leave a review. So please, please leave an honest review as I would love to hear your feedback. You can also check out my profile or more Microsoft related courses by clicking on my name, this link over here. And once you have clicked on it, you're able to view my profile and you can continue your learning journey by going through my top microsoft courses.