Welcome to chapter number 23 of your advanced Excel course I am Miguel Vela from your channel El Tio Tech and today we are going to learn to group invert transpose our data with our power editor it went well we are going to start with this but I don't know before forget that we need to download this gay file it has two sheets to be able to practice so that you can see the video and at the same time practice the link as always I leave it in the description of this same video or you can also go to the page www el uncle text dotcom and there you will find all the chapters so that you can download all the files well let's start now yes but before we are going to interpret this tablet and we also have here product and sales according to it well what we are going what we are going to do first it is grouping well but as we are going to group I want you to observe that in the product we have a keyboard to see how many times it is repeated we are going to go down here there is another keyboard and below to see if it is alone mind there is a keyboard so when grouping we are going to group by unique products and we can do it for example we can visualize the maximum values the minimum values we can make sums counts etc. well let's add then the first thing we do is add this tablet to our editor because we do not select the whole table look here until we select them and we go we already know here to the little data tab not here to add it to the power query data and to the left it says from a table or range or a click here we wait a few seconds we are going to accept them and now yes the power qr editor is going to open look at pq to group our data we have to go to the tab look and transform a click here on the left hand side it says group by let's click here well a new little window will pop up white as you are observing I am going to zoom in a little well look the first thing he asks me here is that you want to group in this case they are going to be products why why what product s they are repeated no and when we group there is not going to be any product repeated well now yes if we go down it tells me the name of the column that we are going to put the name for example here I could put it if I want to be a sum I will put sum by products could be total sum we are going to put the game total sum to be seen confusing here the total sum for products well here on the right it will be the type of operation for example what am I going to do in this case it is a sum no then we select upload to the right hand and as a last option it tells us column, that is, which column you want to add, for example, to the left hand we can make the sum according to the year 2017 is the column or by the column 2018 or columns 2019 and now I am going to put see column 2019 one click here ok look we already have it there we accept we accept and as you can see here we have the total sum by products for example but from the year 2019 beware the total sum of the year 2019 of keyboard sales is 32 1960 if you want it n see in excel simply here they click on close and we click on maintain automatically we already know that excel creates a new table with the query that we just made, not that it was the total sum by products as you can see but look here there are the values there is no problem if you for example here we are going to put sum look at this remove twice click we put your people if you would like to make another query for example we are going to look for the maximum values or we can also be a count we are going to create here look let's create another query ok right click and we're going to hit duplicate let's duplicate well look I'll launch the data this is going to be the count I mean I want to know how many products have been sold simply the number of products that have been sold that is count well we have counted and here look here we are going to go back ok because here it is grouped according to the sum of the year 2019 no then to the right it happened applied we return a milestone we close and look it is going to remain as it was at the beginning we are going to transform a click we are going to group a click and this little window will pop up again ok what we said group the first thing we already know that it is products after that we are going to name it always look by default the count skips me there we have it it may be that there is no problem the operation look it is already selected also that it says here row count one click and here it does not allow me to select anything because why not actually the count is going to do it here in the same column products no and in this case we are going to count for example one a keyboard we are going to download two keyboards it does not say it is going to stay there the same thing it is going to be like two added that more is going to count let's see two wizards here we have another one more to Let's see if there are another three wizards here and we're going to give it without accepting it, we're going to accept it and we're going to close it and we'll give it hold now we already have our data but from the count up to keyboards there are two modes 3 ps3 have been sold l as 23 the doses and so on well more or less we can now understand how grouping works actually it is practically the same as dynamic tables no but you can reuse it many times well this would be grouping we can work in this case well we can work with table 1 or with count there is no problem I am going to create another query better so as not to confuse us ok look at this right click we are going to change its name this was the total sum of remember products of the year 2019 add up to a total of 219 by products enter well there we have it we are going to create now yes we are going to duplicate right click we click on duplicate and now if this for example is going to be called we are going to make a last query we are going to call it in maximum values ok we are going to see which products have sold more at the best price not grouped ends we are going to eliminate this to return to the data here we have it again we are going to transform ourselves we are going to group for a few seconds and that's it Let's see here that we want to group by products this is going to be maximum values we are going to put ok operation we are going to put maximum here for example not a click here and what maximum column values we want to group we want to get the maximum values of the year 2017 for example we do not give it in accept here we have it these are the maximum values that is the maximum times that have been done by product in the year 2017 well we are going to close look we close and we give it a click and now we do have look at a third query look but this time with the maximum values, as you can see, the queries are on the right, don't forget, and you can modify them as you want, not just click twice, for example, you want to modify something, they close, they modify, they close, and automatically the table will also be updated Depending on what you do with each query, that's not how the power query works. Let's now look at invert and when we transpose our data here we have not to invert or transpose, look that transpose basically makes the columns become rows and the rows become columns no we are going to do that with power query we are going to add look at this tablet payment editor we already know the query we select the entire table we go to the data tab we leave from a range table a click here we don't wait a few seconds we give him to accept and he launches us here look he opens the power horn the editor we are going to put here transpose in transpose your name of this query that needs to be transposed look again we are going to go here to transform and I want you to observe that the headings are the years two here we have the first heading product 2017 2018 2019 as a row we have the products no keyboard mouse price of laptop what we are going to do now is that these products that are here in the first column go up now as header rows well let's give then look here we have the option to transpose a click look with a click I want you to see now what we have here and look we have the products here but they are not as headers and apart from that the years disappeared as you can see the years should be here not on the left but they are not so to avoid that they come to avoid that we are going to go back one step I am going to give you a table here transposed I am going to eliminate it with one click and look it returned to normal no what we have to do is lower these headers as a row so that this does not happen so for that look here here in the transpose tab here it says use first row as header one click and we give it to use headers as the first row look so that the headers go down as if they were data we click on it and now if you look product is in the first row year in the first row we have them look there it is see now if we can transpose it we click on transpose and I want you to notice that now we have the headers on the left hand side not as rows and we have the products as columns but these products should be r above as headers we don't know that again we go here and we leave now if we come back it tells us here to use the first row as header one click there and look I want you to notice that now we do have all our products as headers and the years we have it in the first column as rows this is no longer what we wanted to do now if we click on close we click on close and after that we click on keep ok or click here but let them see that now it throws us look at the excel it throws us the products as headings as columns and the years we have it as rows not here on the left well the original data is here look at the product keys sorry they were before rows and now they are headings not columns well we are going to create it comes here we are going to create we are going to duplicate right click we click on duplicate a click there now what we are going to do is go back look we are going to go back we are going to eliminate all these steps we are going to eliminate all these steps and return to normal no there is another one it has the power query it has another option as well in transpose it says to invert rows this one inverts rows in reality simply the products that are last go up first look with a click here a click look what produces them to change the game the first one returns to the last one and the last one is in the first row we don't just give it we're going to put a name maybe it's going to call this one it has the name of invest we don't put it here to invest and we close and keep changes well we already have our tablet here and also with the inverted rows we can count data also look we are going to click here right click duplicate ok look I am duplicating for each query we are going to go here the name and we are going to put here count well this reality is also very simple look I am going to eliminate I am going to eliminate this step because the duplicate of the previous one and we are going to go again to the transform tab and we are going to give it find real rows and we click on count row automatically he only throws a number at you, no, that number is the rows that he just told us, we are going to close him, we will keep that, they only have to leave me, look here for a row, there is not a column with the number 11, basically look in the count query well that would be all actually what I wanted was for you to practice creating and duplicating queries here because each query is independent when you duplicate them and obviously you can modify each of those queries again by entering the power query editor or giving 500 twice times click and we have here all the steps to the right we did what we can until we return there is no problem very well if you have any questions or comments write us here in this video tell us so we can read you and thus support do not forget to subscribe to the El Tio channel Tech and see you in the next chapter.
