[Music] Welcome to this new video I am Miguel Vela from your channel El Tío Tech and today we are going to learn how to import and obtain data from files such as "txt CSV xml Access and Excel" but this time we are going to do it with Power Query so that you have an idea that this is chapter number 19 of our advanced Excel course, this would also be module number two for people who arrive for the first time, we are taking a course from basic to advanced and you are going to do everything find it ordered here on the website El Tío Tech.com is written El Tío Tech.com we are going to have the following presentation and click here where it says Excel courses click here we will go down a little and here we have the three levels we are at level number 3, which is advanced, so one click on see chapters and module 1 was about macros in modules number 2 Power Pivot and now we are at module number 3, which is Power, we are going to click here on the chapter number 19 a cli c we are going to have in your post but below here it says download file here we are going to click and we are going to have all these different files to work with Power Query we are not going to transform anything yet, that is, we are not going to work with Power Query only We are going to import before once how to start transforming our data with Power Query I already have Excel open here I am going to go here to the data tab one click and here to the left do not forget that we understand the little group obtain and transform data, all this data that we are going to obtain, we are going to take it later to the Power Query editor to be able to transform it, but in this video we are going to learn how to import it . We are going to import the first file that we are going to import is the TXT file, not that many times we find it with the CSV extension that can be delimited by commas, a dot plus a space, etc. This is the first one that we are going to import we go to the data tab and here on the left we already know the little group to obtain and transform we can import it directly from here it says from the CSV text or it does not matter if we are going to obtain it from a file and look for what this option is the same gay but we are going to do it from here one click here this little window will pop up where we have to look for our file that we just downloaded from the website this is in chapter 19 let's see I have it here it is twice click and look here it is there it says clearly CSV file click twice another window will pop up where they show us how the data is going to be displayed and they also show us the columns not down here down here we basically have three options you can easily so they don't get complicated we can use it here this option that says load let's try it we'll click on load and the data is automatically loaded here this is from a CSV file in TXT if we go to the lateral right to the lateral right we are going to expand it a little here we have a preview again of the CSV file if we click twice here twice click one two we will jump to the Power Query editor and just here we are going to transform our data we can fill in move remove columns add columns etc. this is an option to import to import the txt files point CSV once you have imported the file and you can already work with pay the neck but let's see other options let's see let's close let's go Let 's open another Excel, let's see another Excel over here, I have it here, a blank book again, we already know if we want to import the small group, sorry, data tab on the left, we are going to give from the txt it joins, although it is the same here, I already have it open again I click on import I click on import and we will see what happens here with some data with some options and we click on load here in the triangle we click and we have load and c load the I'm going to hit load and this little window will skip me this little window allows me or opens more options for example if I give it a dynamic table report then it will throw me in front to apply to analyze or apply a Pivot table that we already saw before is practically the same, just for you to observe, I'm going to accept it, I click on accept and the canon dynamic table appears and here are the fields of the sheet in this case, sorry about the CSV file where I can build my pivot table directly is the option that it gives me when I applied a pivot table we are not going to open another Excel just for you to see it we are going to open free blank and do the same we are going to data on the left from a CSV text again I am going to open twice click look and now I am going to load go to load I click on it and I click on load and again we have here that we can give you a graph it will jump me directly to open to create a graph or create so I connect this option to create only a connection does not show you the data in Excel but allows you to open it directly with Power Query look, I am going to accept it I accept that the files or data do not load here in Excel I do not Instead, it loads in the other one, yes, it appears no, but when you click directly on the connection, it does not load, but here the CSV file appears, only connection, that means if I click here twice, if the payment is going to open the horn with the editor from Power Query to transform my data that is the only difference there is one more option they are actually very easy to interpret options we are going to click on blank book again we go to the data group to the data tab and here from the text one click we are going to select the file again here we have it we are going to load one click and click on load and here this little window will pop up we don't already use it so pivot table report pivot table graph is the same to create a graphic create only connection that the data does not appear here in the Excel spreadsheet but only in the Power Query and here they come here we are going to put it in a table here add this data to the data model if we click here we mark it It will allow opening with the Power pivot as well, that is, it will already have the data model added with the Power pivot, that is, if we add it optional, if you like, mark it if you are going to work with the Power pivot, no, but as we are seeing only the paid editor the Query so I don't see it as necessary to mark it if you click here in an existing spreadsheet you can easily select a location so that the data appears in the location you want if we click here below in a new spreadsheet then it will go to create a sheet here sheet one will create sheets two and the data will appear there the recommendation would be that you just click here on the table and leave it in a new spreadsheet, that is, you accept and you will work here if you want s open the Power Query editor you go here to the right CSV file click twice and here we can already transform our data this does not concern the file in this case of CSV do not forget that this download will be done from the web page here download files and we are going to have these four files so that you can work and know how to import after that as point number two that we are going to import we also have the xml file xml this is a good file that I have not worked for a long time but I remember working on it with a script action with flash animations and all that world but it has nothing to do with this gay let's import the x ml I'm going to open Excel 11 here a new Excel a new blank book we already know how to do it we're leaving to data and it does not appear here in this case the xml does not appear so I go here to obtain data from a file and look to change from an xml file click here here I am in the folder chapter 19 where my files are os and here I have the xml database I click on import I wait a few seconds this little window pops up and he selected the table here although there is a short one he selected it and we click on it so that they do not get complicated so that it enters us it will load us from the front load a click load will appear here there are the data if you want to open it 2,381 rows have been imported if you want to open it with the Power Query editor click twice one two and we will be able to transform our data that would all be very easy no now we are going to import as number three we are going to import to the database of a do not forget also that all this data or those files from different databases we can download it from state pages I tell you this because suddenly they want to grab other databases as an example not what they would have to do for example here is open data in Google or in google.com they put in the search engine open data in my country for example Peru ENTER and the government for example here in Peru there is a web that is open data dotcom dot p you don't access the web and I can download hundreds and hundreds of databases to put them into practice as I want for example google.com if you are from Mexico open data in Mexico there We have it here at home its web that 2.1 punto mx in this way you can download open data free data so that you can use it as an example to explain any class whatever you want you go to Peru here for the Peru website always look for this category that says format one click on format and here you will find different types of databases and different files so that you can work on the case, for example Excel here xlsx pdf you take the CSV file that we already work on xml that is not here one click for example xml access here affidavit affidavit of interest no xml one click and here here it is here it says download one click on download and with that you will get the file so you can work on it That way I downloaded these four files so that you have it more easily with one click, but if you like, access the web according to your country so that you can download different databases so that you can test it, I will enter Excel again here. I have to see to see free blank and we are going to open it we are going to import an Access database the same we go to the data tab we go to obtain data one click in this case it is a database from a database and we search Access are here in the second gay second option one click appears automatically I'm in folder 19 Access twice click we wait a few seconds we wait a few seconds and here we have some calculated fields I'm not interested in those but below I have the little tables we can choose any table that is loading sales of vendors products groups or choose vendors or sales although there are events we have more I think and we only click to load than to load we wait for a few seconds look it is updating me to that in sales loading data we have to wait for the data to load here they are these are the sales click twice and it takes me to the Power Query editor for once work I'm going to close this and let's go to import the last one the last file that is in Excel we go again here data obtain data and it can be from a file from an Excel book we are going to click here we already know here even the Excel also download it from the open data web and select Excel I give it import I wait a few seconds and I will select here for example personal report or data either of those two I am going to select the first one I give it load I wait a few seconds and it is not updating that how many rows are one hundred 1,895 rows were loaded I click twice and I can now transform that data with the leather payment editor in this way we import our data in the following video number four was Excel from an Ex file cel in the next video we are going to bring data from Facebook and we are going to bring data from a web page those two only for general knowledge only so that they know something extra and turned off the leather and not import data from Facebook and from a web page and then From this we already went on to carry out a series of exercises to work with the Power Query editor . This video has been useful to you. If you have any questions, comment on this video, don't forget to subscribe to the Uncle Tech channel and see you until the next chapter [Music]
