Hello everyone, this is Oppadu Excel What is the most important thing when using Excel? function? pivot table? No, it's about managing data in the right structure. Especially these days, as the amount of data that each practitioner deals with increases, the importance of data structure is growing. So, in today's lecture, we will look at the basics of the Power Query function, which can easily automate data-related tasks in Excel, to practical examples step by step. Are you ready? let's go together Compared to Excel, which we use a lot, this Power Query is the same in that it is a program that handles data. These basic operations, such as adding a column or splitting text, are the same, but this Power Query has a fundamentally different purpose from Excel. So, what is Power Query, it transforms data. Data is a tool and program for processing. You can think of it as an essential function of Excel for simple data processing. As the amount of data increases and becomes vast, how to manage and process data becomes more and more important. As a result, this power query function is becoming more and more important. And if you want to use Power Query, the Excel function that must be connected is the table function. To load data with this Power Query, you must change this range to a table or a name definition range. In the future, even if I don't think about it, I think the Power Query function will become as essential as the pivot table in the next three years. Can Power Query only process data? No If you use Power Query, you can easily automate more than 70% of the work required in practice with Drag & Drop. What this means is that most of the tasks are repetitive tasks when we see them in practice, and to automate these tasks, the most commonly known program is AutoHotkey, using a complex program like this, or getting some programming help from outside. Instead of using Power Query, you can automate more than 70% of tasks by simply clicking or dragging the mouse without any complicated functions or coding in Excel. If we look at Excel, just like there is a home insert drawing page layout like this, Power Query is the same. Rather, the menu has been simplified. only a few buttons That's why you can implement most of the features using only the buttons on the 3 tabs to add a home conversion column. In my experience, more than 95% of what is needed in practice can be done with functions alone. Another advantage is that Power Query records every operation step by step. You will see a cog button like this. You can easily edit it by clicking this button. If you look at the screen, it must be a screen that many people are familiar with. It's like a macro Yes, what it means is that we did something with Excel. Every time I did a job, I recorded it. That's why I said earlier that by using Power Query, you can automate most of the tasks required in practice, more than 70% of the tasks, and that's why. This is not a macro recorder because when you use Power Query, each action is recorded at each step. It's not a recorder, but if you use Power Query, you can modify or edit each step through this task and easily change it as needed, or you can easily automate it by adding tasks. Then, let's see how we can apply Power Query in practice. From a practitioner's point of view, most of the tasks were downloaded data. You must have downloaded the data from the central management program, right? Then we will process this data How do I get this raw data, it would be great if I could use the raw data as it is to make a report and report it to my boss or director But it's really rare After receiving this data, processing is required anyway. However, if we say that we do this in Excel, we will see how to do it step by step. is the first I have an order number Here, the first of this order number represents the country, the second is the year, and the third is the customer number. Let's say you want to receive this order number. Then you have to take this data and edit the original data. I'm going to extract this Second, by referring to the delivery date and the order date, the delivery date is calculated in a separate column here. Third, if you look now, the customer name is like this But if you look at the customer name, it is SK Group, and each group has an affiliate. But when we report, if you look at it now, it's the same group. That's why I want to tie this group together. One SK group, one Daewoo group. To do that, if you look at each group now, they are separated by underscores like this. So, based on the underbar, only the values in front of it should be printed and bound. You'll need these tasks too. You have to do these tasks one by one. How are you? If you do it in Excel, you have to edit the original data. Then, if there is any problem, first of all, we will calculate the delivery date by referring to these two dates. Let's see. We received raw data. Originally, raw data is ID, order number, order date, delivery date, and after delivery method, data is raw data. But to make the calculation easier, I added the delivery date in the middle. Will that be the case? Can't it? No Most of the data we deal with in practice is cumulative data. I added a new column in the middle Then, after downloading new data, the processed data is in a different order, so you have to change it every time you add data again. right Therefore, it would be impossible to work by adding a column in the middle like this. I'll see you then Data will be added next week or next month or every day. Will you copy and paste the data every time new data is added? Further, when we use a function or fill data with quick fill, when new data is added, we copy and paste the same function or repeat the same operation There will be problems that have to be done. I mean, how we do this in practice is to do the work we need to do in a notepad or memory in my head. If you have downloaded the data First of all, you have to subtract column C from column D for the delivery date, right? Next, you need to get this eigenvalue from the order number. To do that, you have to get it through a function. the formula is like this I'll make a copy and paste for you This is how I have this process in my head And every time data is added, the same task is repeated over and over again. In fact, in practice, this takes up most of the work and is a huge waste of time. These tasks cannot be automated unless we use VBA or a separate RPA tool if we use Excel. But if you use Power Query, you can easily automate it with one click of the mouse. But in order to use Power Query well, there is one thing you need to know. You just need to know the right data structure In other words, it is called data normalization, and this content has been explained several times in previous lectures. And since I have prepared this content as a video lecture before, if you are curious about this data normalization, it would be good to refer to the previous lecture. How it works, it works like this I got the data And how are we going to process it based on the data we have received? And I will send the processed data to Excel, to Azure or the cloud, or to PowerBI and print the result. In the middle stage, we can see that we are processing data into the required form and performing the operation of collecting data. Before proceeding with the exercise, you must first download Power Query. After coming to Google, I'll search for it. I'll search for Power Query add-on download. Then you will see the Microsoft homepage in the first search result like this. I'll move here Then you can download it like this Depending on the version of Excel you are running, download Power Query from here and then run it. How to check whether the version of Excel running here is 32-bit or 64-bit? Go to Excel and then go from File to Account And then, if you look at the Excel information here, you will see bits like 64-bit or 32-bit in the middle. You can install it after downloading the file according to this bit Then come to File and then to Options And come to Add-Ins. From here, select Excel Add-Ins and go to Go. After that, if you look below, you will see the Power Query add-on. After selecting and clicking OK, you will see this tab called Power Query here on the right. And in the case of users of 365 version or Excel 2016 or later, when you come to data, you will see Import data here. You can think of this function as the same as Power Query. And for users of Excel 2013 or earlier, if you install the add-in, you can see that Power Query will be installed on the right side like this. Let's look at the header now. The header is divided into two lines like this. However, since the data created with two headers like this is incorrect data, I will replace the data with the correct data. how would you change We will refer to the original data and create the data outputted by the query here. If you look at it now, you have goals, performance, and sales profit. In addition to this, let's assume that you want to see the percentage of achievement because you have a profit margin, performance, and goals. Then it won't be a problem if we do this once or twice or just once or twice. But let's assume that this is a daily sales report. Then, after receiving this file, if you look at it now, the cell is merged into the department in charge, right? After unmerging this, you need to calculate the profit margin next to it. On top of that, we have to calculate the achievement rate, right? Also, when data is added, after copying the data and adding it to the bottom, the column order is different now, so cut it again, copy and paste it, and repeat this operation every time. Instead of repeating this, if you use Power Query, you can easily create and refine the data even if there is a header with cell merging like this. how? You can make it with one click of a button, so let's take a look at this. Import data with Power Query. Remember that the first thing you should always do is change the Excel range to a table. So I'll choose this range like this If you look now, there are two headers. Wrong data, but that's ok. This data will be corrected by Power Query, so please select the range with this incorrect header in it Then, after coming to Insert, I'll click this table or press the shortcut Ctra+T to create a table like this And here you will now see the header included. If you look now, the header is wrong. You can't include headers So, uncheck Include head and click OK to create a table. If you look above, you will see the column number like this But I will hide the column number because it is an unnecessary column number. So you select a table. When you come to the table design, you will see the header row here. If you check the header row button like this, your hair will be hidden in this way. Next, the table is now styled like this, and now this striped row looks a bit ugly. So I will change it to the same shape as the existing range. After coming back to Table Style in Table Design, you can see this brightly in the above. I'll select this with No Table Style. And then, if you look on the left, you will see the name of the table. I will change the name of the table. I will change the name of the table to Sales Daily. So, what is the table we made now? It was originally a range, right? This was the original range, but if you change it to a table, the cells merged in this way will be released and the table will be created like this. In this state, we will transfer this table to Power Query. So, after you come to Data, in Import Data, go to Other Sources, and then click From Table Range, or under Data, you will see From Table Range. I will click this button If you just click, the Power Query Editor will run like this. Then this table we just made is transferred to the Power Query Editor like this And let's look to the right If you look on the right, you will see the steps applied like this Let's see it step by step I'll come to the first step I'll click on the original This is how we loaded the original data, right? And then, what is the promoted header, in the original data, Power Query assumes that the first row will be the header by default and uploads the first row as the header. So, if you come to the second step, the header will come up like this. Next is the changed type. What is this? Let's look at the left side of this header now when Power Query judges it. If you look on the left, here are the data types ABC, ABC, ABC 123. So what this means is that Power Curry has a given data type for each field. And like this, the first column is the character The second is a letter, the third is a letter and a number, and so on, and so on, we're going to automatically change the data type, right? When you come to this promoted header, it is all numbers and letters, and when you come to the changed type, you can see that the first and second columns are changed to letters. But the data is not the type of data we want That's why I'll delete all the steps in the middle. If you click the X in this way, this step will be deleted, right? I will delete the step and come to the first original data, then process this data. There is one caveat. If you delete this step, Power Curry can't go back to the previous step if you delete step 2, you can't go back Therefore, before deleting a step, be careful to delete it after reviewing what the step is. However, did I mention that the step is not recoverable and that Power Query loads the original data? So the steps cannot be undone, but the original data remains intact. So, in fact, even if the step is not reversible, it is not a big deal, but it is not reversible anyway, so it is better to proceed with caution. First of all, the first row in the header is an unnecessary header, right? The header we should see is the header in the second row That's why I'm going to delete the first row You'll see Remove Rows after you're home. Here I'm going to select Remove Parent Rows. Or if you look at the top left of this table, you will see a button like this After clicking this button, you will see the top row below here. You can see the same by clicking on this And the number of rows we want to delete is one Since it's the first row, I'll delete the first row. I'll add 1 to delete it like this Next, you can put this row in the first row as the header. After you come home, if you look at the conversion, you will see Use first row as header. Click this or same, after coming to the left here, select Use first row as header here. Then, like this, the first row is uploaded as a header. We'll see later. The original range we had had cells merged like this. But when I turned this into a table, I got an empty cell like this And when I load this data as it is, there is an empty value in the middle like this. So I'm going to fill in these empty values how do you I will select the column with empty cells Then after you come to the transformation, you can see the fill in the middle of the transformation. In Fill, you can fill in these values in the downward direction. In this way, if you select Down in Fill, the departments in charge are filled one by one. We originally had only goals, performance, and sales profit in the original data, but the data we will see in the form of a report will calculate the profit margin on the right side and add the calculated value of the achievement rate. I'm going to add a new column next to it. Look at the tab above. Add Home and Transform Columns If you look at this transformation and adding a column, the values in it are similar. If you look at the button, there are columns and numbers for conversion, and text to numbers for adding columns, and so on. What is the difference between these two? Transformation refers to the currently selected column and replaces the values in the column. Transformation is just changing the value inside, or splitting the text, or something like this. Adding a column refers to this data and adding a separate column is called adding a column. Now, what we need to do is calculate the profit margin by referring to the sales profit and performance. That's why we'll add the profit margin next to it by adding columns. If you come to Add Columns and then come to Standard, you'll see a break here, right? I'll click split We're going to calculate the profit margin, and this margin of sales is going to be sales profit divided by performance. So, we're going to divide the profit margin, and the value to divide is the value of the column here. Where will the heat be? It's the performance column. This will calculate the sales profit divided by the performance value. Click OK. Then the sales and profit divided the performance was calculated like this. And if you look at this format now, there is 1 comma 2 like this by default. This is a number But the value we're going to display is because this divided value is a percentage, so I'll change the format to a percentage here. And then double-click this header or press F2 to edit the header. In this state, I'll change it to the profit margin, okay? I'll see you later. Next is the achievement rate. The achievement rate will be the performance-dividing goal, right? I will choose the same performance In that state, I'll come to the standard in Add Columns and then to Divide. And this time I'm going to share this performance, and the value to give out is the value of the column, and the reference column is the target. And when you click OK, the achievement rate for the performance is also calculated. In the same way, the display format is a percentage, and the header is changed to the achievement rate like this. Then, there was the original data like this. The data has been changed correctly, and in addition to that, I created temporary data that additionally calculated the profit margin and achievement rate. And it was automated like this for each step. Then it's over I'm going to click Close and Load after coming to Home. If you just click this button here, this data we just created is saved in a table format, and it's saved in a new sheet. What we're going to do now is create a table next to the original data like this. So don't click close and load right here Click the arrow below and select Close and Load As And then I'll add it in the form of a table Select a table. The place to insert is an existing sheet, I will add it to the existing sheet next to this And I'll click OK. Then there was the original data like this, but I didn't edit this data. We will create this by adding normalized data + the data we want to see through Power Query while maintaining the original data. And after selecting the table, come to the data. If you select Query and Connection, you can see it in the form of a query like this or when you come to the query here, you can see it like this. In this state, you can edit this query and connection by right-clicking on it and coming to Edit, or by double-clicking this button. This query is called by referring to the table here. And if new data is added below this table Then this query will be updated automatically by referring to the values in this table. Let's take a look. The February data has the same format as this data now, so I'll copy this range like this After copying it, I will paste it here after it comes to the table in January, so please do not paste this value as it is. because there are merged cells in this range If you paste this range as it is, the range cannot be recognized in the table. That's why right-click here and select and paste here. I'll paste the copied range in the form of a value. I'll paste it in the form of a value. That's it, the data has been updated. The table expands with this increment of angle brackets, right? And the query will be created with reference to this expanded table After you come to this table, if you make a selection, you'll see the query here, right? Have a query. Let's click refresh. When you click Refresh, the added data is automatically reflected. Korea says that the profit margin is not in %, but you can change that easily. First of all, these goals, performance and sales. These values will be numbers. I will select from column J to column L like this. Then, the shortcut key is Ctrl+Shift+1. I'll press the number 1 key. Then it will be automatically updated with currency format. Second, the profit margin and achievement rate will be percentages. So after selecting columns M and N, here's the shortcut Ctrl+Shift+5. 5 is a percentage, right? Since the symbol is %, pressing Ctrl+Shift+5 will update it as a percentage like this Let's change the column like this If you add data and then refresh this query after adding, the table width automatically changes like this If you want to fix the existing column width without changing the column width when this table is refreshed. After selecting the table, I will come to the table design. And if you go to properties from here, you will see Adjust column width in the middle. Uncheck this. And click OK to change the column width like this And then after adding data, if you refresh this table, the column width will stay the same. So, in this way, we can change the data to normalized data while maintaining the existing data, and then add the data we want to see and reflect it in real time.
