Welcome to chapter number 22 of your advanced Microsoft course I am Miguel Vela from your channel El Tio Tech and we are going to continue look with module number 3 where we are seeing everything related to power fueguino what we are going to do today with this video is to manage is to combine and append our data with the editorpower query before starting this class obviously we need to download look at this file so that you can practice watching this video for all the people who come to the channel for the first time look at all the chapters and the files so that you can practice we will post it on the page the dotcom guy writes the dotcom roof we are not going to have look at the following presentation and we are going to click on it where it says courses click here ok we are going to wait a few how many seconds we go down a little bit and here are the three levels we are going to click where it says advanced Excel click here because here we are not in the ava level Launched then we have here look at module number three power query has not yet published chapter 22 but when you are watching this video obviously there will be a click on chapter 22 and we are going to scroll down a little and here it says look download the file of gay chapter we click on download and it will throw you a file similar to this one ok it is compressed it is a good weird ok it is simply right click and we give it where it says extract here then I am going to extract this folder look and here we are going to have the two files with which we are going to practice well we are going to open the first one which is to combine look I am going to close this and we are going to open the combine twice click here let's go with our first exercise look the first thing that we have to do is obviously add this data to the power query editor and we already saw that before let's look with sales 1 and sales 2 but we are first interpreting the data 1000 in sales 1 look in sales 1 we have a column d e products and the columns on the right are by year ok and their sales for example 2012 2013 2014 2015 and 2016 look here on the sales sheet 2 we are going to click it is the same structure but what is not repeated here look look at the year here I finished in 2016 and here in sales 2 they start from 2017 to 2021 that is not the difference what we are going to do is join these two tables look with the power editor out and so that only one remains consult well let's start with this first we go to sales a click on sales we select the whole table we go and we already know the data tab and here it says from a table or range or a click here we are not going to wait a few seconds we give it in accept, we click accept and look, the power query editor is going to skip me and we already have our first query here, ok, when we look and insert the data to the power query, a query is created, so the power query determines it or calls it, it does not query look here the name and we are going to put a query for example well we are going to put a query one I am going to close here well I am going to close here and we give it keep changes here and keep a click automatically we already know that a little page is going to be created look at the query it's fine here let's go now with sales two look let's go with sales two and we do exactly the same we select everything we already know where to go we go to the data tab and here where it says from a table or range a click here again we accept and here it ends enter the data to the leather power editor and we are going to put this name on the right we are going to put query 2 ok we close we close and we hit hold I want you to see that the two queries jump to the right hand side we don't already know how to give a click or twice click sorry opens the power editor to transform our data again query everything we click twice and it also opens here see look we are going to click on any of these two queries we are going to click look we click and automatically at the top a new tab is enabled that says query we click on query and here we have a series of tools that we are observing there was not a series of tools that are very similar if we give for example here in the query right click these options that are here by right clicking look we have it here ok here they are in the query tab look here we have some tools we are going to see it now but let's go in parts we are going to focus where it says combine here we have the combine tool and the append tool well the combine tool you basically go from one batch combines columns ok columns the append tool does the same thing, that is, it joins you to append it combines you but this time rows ok so we already know how to combine join to columns and append from an entity join as you want to call it rows then we want columns so we are going to click on combine a click here will jump them look at this little window here we have and look we are going to select first query one below we are going to select queries two ok and apart from that we are going to select look at products and here we are also going to select products once we have selected both we are going to click on accept ok we are going to click on accept and see how the power query has just joined all the columns of both tables but look here it says consulted here we have to expand or click here and look at it we accept and it has just expanded as how we are observing we do not have there are consulted 2017 2018 are consulted that it says products we do not need it we can eliminate it no so we give it here remove column remove columns complicate it already removed us look now if we already have from the year 2016 2017 2018 we are not going to close here look we are going to close here and we are going to give him maintenance he is going to create us look at a sheet number 3 ok why because we finished to create another query look what it says combine to this name combine we can right click and we are going to change name from there change name and we are going to put I don't know a combination for example I can't it could be like this combination to know what the combination of query one with the consul 2 well I want you to realize something here in sales 1 look at the sales data here 1 we are going to click on the left side not in the first column we have exactly the same look at the same data as we have 2 in sales look they are exactly the same well but what happens if we add a new product we are going to add it we are going to put the product here in the Trojan product well we are going to put 100 soles on everything quickly look 100 soles on everything ok that's it and now if what we have to do now is go look here query 1 click and click again here in the little query tab and we are going to update a click here if you have several queries I recommend you that you go one by one queries two you go to query and again they also tell you to update well the same thing we do here in combination because it is taking both columns as reference both queries we click and again in no procedure query and update well we will see what is happening look it has just been added here here we are in combination here on the left the product that we have entered has just been added not that it is a product test how only this product look that it is here on page 20 a its not only there are sales 1 but there are no sales 2 look it doesn't exist here ok then look how it shows us the data to see look how our data doesn't add the product but in the right hand in the other years as it doesn't have look how it doesn't in this case it has data or the product does not exist in the following years it does not appear empty ok it appears empty the same thing happens if we double click twice on the power query editor no m See here the last one says test product look until the last test product and here the consultations of the following years do not have data well then we already understood what is happening I am not going to close this look I am going to close this and now we are going to open it to annex ok twice click here on annex and this is the next exercise look in this exercise we have the same sales 1 and sales 2 but this time we are going to annex we are going to join all those products to these products but look notice that here it says the year 2017 2018 2019 2020 2021 we have the same thing here 2017 2018 2019 2020 2021 that is, the tablet is the same treatable it is the same but it varies here in the products there are several we have a keyboard mouse pc and these products do not exist here they do not exist how can they realize what we are going to do now is also combine but this time we are going to use the append tool look we are going to select the entire table we already know the procedure we are going to data we are going from a table range one click to there and we give you to accept the same procedure again as who we practice to what name are we going to give consultation point again ok we close here in the x don't forget we close the x and we give it to keep ok look here it creates logic for the consultation so that it is updated and we go now if to sales we select everything again we go to the data tab and after that we go from a table or range a click here we give it accept look we are going to have the power pq editor again query and we are going to put query 2 ok we close and we give it to maintain until now we did not do anything look we did nothing the only thing we have done is add to the horn power editor and create the two queries a click on any query in any reality and we go to each little tab consult here one click we already know look we are in the little group of combining now to join or to add more rows what we are going to do is append ok we are going to click here on append or a click that will pop up that little window and we are going to select now yes look at cadiz two tables if they were appending more than two tables then here not from three to more tables but we are only working with two so we select here consult one and here consulted no this is the name of the query that we have created simply in accept and look look look what is happening and I have just added the data here short mini we have USB charging and the 15 data I already have only one as you can see minimalist not in just one, that is, I have put together look at the joint where it is let's see sorry I'm going to close this I'm going to hit maintain again and look the third column is going to be created not here until it says add one if we go to sales one look at usb and it ends in speakers not here in sales 2 there are neither usb nor speakers good but what happens if we create a new column here again look again we are going to do the same we are going to create a new column here in 20 22 ends and we're going to have it I'm going to add 100 to all this again and if you don't look at 2022 here in sales one doesn't exist in 2022 why because it 's only until 2021 and here we have until 2022 let's see what happens look we have to click here on query 1 we are going to update you or gay don't forget select the query and the thought of query we give you update we go here we give you query update we give you append here what is what we are uniting and also we give here to update look that here it has just been created for me sorry I got very close look that here the year 2022 has just been created but here there is no data ok you can realize that there is no data but below if you realize why because only in these products there are sales of the year 2022 if we open the power query twice click look also here it will appear in the same way the year 2022 in your products I do not have data gain those products there are no there are no sales ok because just look just for me Given that your products have been entered in the year 2022, they are well below and this would practically be everything. I am going to close here with append and with combine, other than that, we can join, we can join columns , and we can also join, we can also join rows, come again, let's go to Iraq in one click in any of the queries and we click here in query there are more tools look how you can see what we have just used is to combine the append and here the update not here in reuse look we have two ones is to duplicate ok create a new identical query to that query for example to this query one and I can duplicate it look I click on duplicate here and again it takes me here to the power and I am not going to close I give it maintain and look here another query has just been created here that is this query is a copy of query number 1 ok it is the copy but since we have duplicated everything you do in query number 2 it will not alter query number 1 that is there will not be any reflection at all or only query number 2 is going to remain we are going to eliminate it look here in query one click and we will eliminate it here do not look here until one click we will eliminate it and eliminate and we will eliminate the query that we have just duplicated again one click on query 1 we we go to the query tab and here it says reference this reference creates a new query that refers to the duplicate is common it is as if we were duplicating the query but this time everything you do in this new query will be reflected in it no look we are going to give a reference click we close here and we give it keep look now yes now yes look here it says query number 2 everything that I do here is going to have changed in query 1 ok that is everything we do in the query that we have just duplicated but this time as a reference it is going to alter query number 1 no that is the difference between duplication and reference after that here we also have properties look if we click on it it will skip this little window to change some properties of the query well we are going to see this later I don't want to soak up a lot of information I am going to close it yet it is not very important well and in editing here the edit option if we click on it look at us it will open the power, that would be all, not, as you can see, this little query tab is not very scientific, the options are very simple, it allows you to combine, it also allows you to append, duplicate, reference, update, delete properties, edit, here we have some more tools that we are going to do to see later we are going to stay here if you have any questions if you have any questions you can comment on this video to answer you and thus help do not forget to subscribe to the El Tío Tech channel and see you in the next chapter.
