Welcome to chapter number 21 of your advanced Excel course I am Miguel Vela from your channel El Tío Tech and today we are going to see some exercises that are actually quite basic so that you can become familiar with the Power Query editor we are going to start with this but first what we need is to download this book with these three pages here we have on the first page the questions to solve the exercises and here we have the little tables that we are going to use exercise 1 home your table exercise 2 also here is your is let's download it. For people who come to the channel for the first time, we can download it here on the page they write to hotels.com, we are going to have the following presentation, click here where it says courses, click here, wait a few seconds, go down and we are now with this video at the advanced level so one click on see chapters and here are basically the three modules that we were up to now module 1 macros 2 Power Pivot we are now with module 3 with Power Query chapter 21 one click here on chapter 21 and We are going to download here to download the chapter file, click here and we will obtain the following or the following Excel book. I want you to observe that here it tells me the sheet that we are going to use, for example, it is the question of the question that goes to the exercise 1 exercise 1 is here below each exercise 1 and exercise 2 let's go with exercise number 1 as who we already started and started working with the Power Query editor we are going to change the name we are going to change the name of the query and the description so we already know change the name change description consul sorry and put at the end it tells me the first row as a header let's do it this is for the sheet that has the name of exercise 1 let's go here to exercise 1 one click and we are going to select we are going to select all this data we select them to see a little me, Go here more we select all the data there it is after that we go to the data tab data tab and to the left we have the group to obtain and transform data after that here It says from a table or range we click here we wait for a few seconds until it opens until the Power Query editor opens what is asked here first is to change the name of the query and then the description that is here they come here to the right where it says properties here in name we are going to change the name we can put my first exercise this would be the name of the query that we are going to identify when we are in Excel after that down here it says all the properties one click here this little window will pop up here we can also change the name and we can put a description obviously the description will be a reference to the name for example we are going to put my first exercise with the 'tie-break' for example we click on accept and with that we already change the name of the query with that we already change the description of the query as we see it we close here we are going to close one click another window will pop up and It tells me if we want to keep the changes, we click on keep, we click on keep and curiously I want you to observe that now we have sheet 1, another sheet has just been added, that is, it means that all the changes, all the transformations we need to do in the Power Query will only be reflected in this sheet, it is as if a copy were created, all updates, everything we do in Power Query will only be affected here, cos Contrary to exercise 1, which is where we get the data, is not affected at all, this is how Power Query works and in this way, you can have a backup copy with all the transformations that we are carrying out to the right to the right side, they will go to be my queries here is the name that I just put there it says my first exercise that I am going to get a little closer, there it says my first exercise if we hover over it it will jump to the left as a brief information of the table to open the query again twice click here they come twice click one two and again, we are in the Power Query after that it has also asked to put the first row as header because it asks for that because many times many times the Power Query you may have this problem that the first row appears as headers as it is in this case in this case there is no problem because the first row is as header and contains u is the first row and In this case, if you have taken it as a header, click here twice in the Power Query editor and there is no problem here, but if that is the case then here in the little tab here let's see I'm going to go to the home tab here it says to use the first row as header what happens if I click here one click look here in the header it will change capital I click to see use here and now I get these numbers the numbers of the bone table is taking now yes the first row that is showing me the Power Query as in but obviously I want that because it was if we look to the right here it says applied steps here will be all the steps that you make all the changes that you make in other programs will also be it cooks like the history the history of everything you do here it says promoted headers we are going to delete this and we are going to delete promoted headers or it is necessary that you delete it I am going to put here in origin a click and again the data returns another Once the headers appeared correctly but if I give promoted headers here again, how will a click change and in this way I return to the tool that I just used, which is to use the first row as a header but I need that, I am going to delete it this is how you work with Power Query here will be all the steps because you can undo what you are doing but through applied steps if you can undo it one click here we eliminate it and until correct this is question number u I am going to close it we are going to questions to the remove questions and the exercise is now sheet number 2, that is, it is here, he tells me to see, let's read it, establish the format of the data in the column, that is also very easy, let's go to exercise number two, but let's open a second query I'm going to select all the data s let's go we already know how to think everyone s let's go from a range table click here here we have query number 2 I'm going to put the name my exercise v We are going to put two kings as you want that the name really matters for this case as you can see here we have code and product we have price quantity and total what we are going to do here is change the format we can change it if we click here where you see abc one click and in this case we are going to select the type of format that corresponds to this column in this case we have numbers and we also have letters one click here and we are going to put the text here there we have it correctly again if we click on 1.2 one click here on price and in this case the price is a currency so we have to give it in gay currency and in exchange for quantity one click here to see if that little window is skipping me we will replace the current one click here and in exchange, the sig of the currency has already been entered and below I also change, we are going to click where it says 123 and amount is an integer come here, click here, we have it, total, it will also be currency, click here and we will give it currency to me going to jump r this little window again and we click on replace the current one and in return we can also change here in the home tab in the home tab where that type of currency types of data sorry click here and select the type of data that would be in this In this case, we already know that it is currency, the same in quantity, one click, one click here, type of data , we can give you an integer, price, one click here, type of data, and in this case, we give it currency, so it applies to all the columns, as you can see, it is quite basic. it has a lot of science we are going to close here we close with click we click on maintain to maintain and our little table s has just created another table this table is from query number two that is from exercise number two and here we have up to two the two queries of my first exercise and exercise two let's go with the third question it tells me to divide it tells me to divide column of code and product delimited by the hyphen and by characters gay characters again divide column code and prod ucto delimited by the script we are going to see the table here at home s table is delimited product code by a hyphen so we are going to divide according to this script we are going to create two columns a column for this code and a column for this product how we do it With Power Query it is very easy in this case it is query number two that we have exercised two twice click s the Power Query window will pop up and we are going to place here where the code and product are because that is what s is asking for that you divide according to the script you mentioned that is in the center then here plus the start to beards at start s we are going to divide column or click here we have a series of options for example delimiter can be a comma can be a space can be in this case the sig of the script or it can also be by number of characters by positions we go with the delimiter because in this case we have a hyphen in the center one click here this little window will jump and here we have to select or write the delimiter one click and we have a series of limiters as you can see “:,=” portico tabulator but in this case we are going to give it custom because we have that option but you can also see that the script is skipped automatically by the Power Query editor since detected that there is a hyphen in all in all rows that could be a possible delimiter in this case it is true it is correct we can put the points with the semicolon according to what we have here in the rows what we are or the delimiter according to the delimiter Sorry, below it says divide delimiter located furthest to the left of limit located furthest to the right or every appearance of the delimiter, that is, if we put here in the third option, every time there is a script, it will create a column, so that's what we want, we will give it a delimiter located furthest to the right, that is , so that it starts from the right to the left or also a delimiter located furthest to the left, that is, from left to right and I am going to put it from the left it gives more located to the left and we simply click on accept what is happening now I have two columns in a very fast way as you can see with Excel it could easily be done but we would have to do it by means of a formula now we use formula for Now, as you can see, we have here, here we have it, and after that, I'm also going to close, I click on maintain changes that must be maintained and that here now here on the sheet it already divided me into 2 columns, as we are observing, we have changed the name We are going to change it before we forget to click twice on my exercise 2 and here we already have it in the header right click and we click on change name casta and we are going to put code ENTER right click change below change name and here we are going to put products ENTER we are going to close this we click on maintain and that here also the code and product have changed for me we are going to go again here we are going to go here again but before we forget iden to the question and by characters it tells me by characters we are going to see how we do that twice click we are going to return here to the Power Query and if we locate we are going to locate for example here where it says product again home tab and we give it divide column but this time we are going to divide it by number of characters, one click here that little window will pop up and I am going to put, for example, 5 characters, gates, I am going to put 5 characters, that is, here so that you understand me more, it says divide once at most the left as possible, that is, from left to right, that is, so that I separate the first five characters, or it could also be here once, as far to the right as possible, that is, from right to left, if I put five I will count from right to left 1 2 3 4 5 has 6 we are going to put 6 so that you can see how it practically creates another column with 6 characters from right to left here as far as possible to the right we click on accept and now the 6 has just grabbed the data first ca characters from right to left after that forget that here in applied steps we can return to the origin one click and we return to the origin we can also go to divide by delimiter until we divide by column by delimiter and again we return to ca or if we go to the last one here where it says divide column by position or click here and we went back to what we just did, I'm going to close, I'm going to discard these changes because I'm interested in saving them, I'm going to discard them and now we're going back to the last question here, replace values of the column products tells me exercise number 2 we are going to click twice here on query 2 and we are going to replace here we click here and here it says replace the values we are going to click here if this little window is going to pop up and what values we want to replace, for example, I am going to replace milk supposing and I want to replace it in this case we are going to put El Tío Tech as we are going to start with this word for all the words that have lec I have in the line we simply accept and now if there is only one reality that uncle tec made we have just replaced it and here on the right I also get the step I can delete it I look at it I am going to delete it one click and again it returns to normality that is everything you do here on the right in applied steps you can easily return if you like and this tool is really very simple again replace the data for example we are going to replace in this case salt we are going to look for a value we are going to put salt and We are going to replace it with sugar, we are going to accept here there is only one salt and we have it, we are going to accept it and we will replace it at home . We are going to close it, keep changes and we are going to stay here. Actually, we have used quite basic tools so that you become familiar with Power Query. If you have any questions, comment on this video so I can answer you and don't forget to subscribe to the El Tío Tech channel. and see you in the next chapter. [Music]
