Excel Power Query #04: Import Multiple CSV Files In 1 Step & Retrieve New Files Automatically

welcome to power query video number three hey if you want to download this file as well as some CSV files that go along with this video click on the link below the video now in this video we want to see how to take multiple CSV comma separated value files from a single folder and import them using power query now power query is a free download Excel 2010 or 13 you go to Google and say download power query it's easy well now let's check this out power query is going to allow us to just say look at a folder it will go and get these and combine them into a single table but check this out later when we don't new files into this folder if we refresh power query in our pivot table everything will update it'll be absolutely amazing all right now let's go back over here we're going to start by going to power query from file from folder we have our browse button we click we can now navigate and there's that folder inside that folder are three CSV files I'm going to click OK now I'm going to click OK a second time now the query editor opens up I want to come over here and name this all city data and enter now we got a bunch of columns based on information about those files for this video we don't need any of this and later videos we'll see how to extract some information from like file name all we want is the content column so I'm going to right click and remove other columns these steps like remove other columns we can see them listed here you can actually go back any time through the steps it's remembering them and those steps will be saved and that's how it knows how to do this later when we refresh now let's go over to view an advanced editor and check this out you can watch this being written so source fault there's the folder path and so far all we've done is remove other columns based on the content column all right now I want to click this double downward pointing arrow to expand the table now a couple things right off the bat it looks like it got the field names from the first table we're going to have a problem with some of the field names from the other table but first I want to click on City go over to data type I want to check out each one of these columns because sometimes when you're importing it'll treat a date or sales as text I see that it's interpreting it correctly look at that this one I'm going to actually change it to text because this is never going to be used as a number now let's go check out and see if we can figure out how to get rid of down here those records that show the field names because we'll get an error it'll be text text text and you can't have a date and text in the same column without getting an error so let's click the drop down here list may be incomplete I'm going to load more and check that out it sees down there somewhere that their city remember I'm going to click escape when it sees City here it will see date sales and store ID we can do that over here also just to check it out load more and sure enough store ID is in this one we won't be able to do the old trick on these because their dates and we'll get an error but let's just filter this boom when I say city it's writing in the query everything that's not City and this will get rid of our field name records which will cause problems click OK now let's go look viewe advanced editor alright so we have combined binaries and check this out there's a CSV document that is a function you can actually directly use an in later video we'll use that table promote headers and here transform and check this out it got the first rows header city type as text each one of these columns has a field name and a type and then there's our filtered not City I'm going to click done I'm going to go to home close and load to now here table new worksheet that means we're dumping into Excel if I want to create a connection only and if you're in 2013 you can add this to the data model we're not going to do that I'm going to click table and uncheck add to the data model and then load you can check out the query it's going through all the rows all the files and there it is six hundred and sixty-four thousand rows if I ctrl down oh you gotta be kidding me ctrl home now let's build a pivot table because it's going to be amazing we're going to have this awesome report here we'll just update and we'll instantly go out and get new files which we'll put in there alright so pivot table insert pivot table or the keyboard alt NV and I'm going to put it on this sheet just so we can see it all in action I'm going to put it in f1 click OK alright store ID down to rows sales down to values right click number formatting not format cells currency you know with millions we don't really need to show decimals click OK design report layout outline or tabular I'm going to select outline I'm going to close the field list now I want to add a slicer whoops right click number for me what happened there oh I clicked number instead of currency click ok that's much better now we want to go to analyze and we want to add a slicer for City click OK and the cool thing is is we're going to get some new data from cities we're just going to refresh the pivot table refresh the slicer will refresh everything more fresh all right I'm going to save this control s alright watch this right click paste I'm adding two new files and I'm going to do this in two steps if anyone knows how to do it in one step you know I I always use or I like to use data refresh all and the keyboard for refresh all is alt ctrl alt f5 which I keep trying and hoping the pivot table will update ctrl alt f5 you can see over here that's working for the query Wow look at that 800,000 rows and were done right-clicking the pivot table refresh and boom check that out that is just amazing power query is it not the coolest thing ever now I can do Tacoma it went and got that San francisco-oakland Portland so power query to go and get multiple CSV files build a pivot table add new files and instantly at all updates all right we'll see you next trip

Channel: ExcelIsFun

Views: 86,111

Rating: 4.75 out of 5

Keywords: Excel Power Query, Excel 2010, Excel 2013, Mike Girvin, Michael Girvin, Mike excelisfun Girvin, excelisfun, Highline College, Data Analysis, Power BI, Microsoft Power BI, Import Data, Transform Data, IMPORT multiple CSV Files, Power Query Import From Folder, Edit Query and look at code that is written, Build PivotTable with Slicer, Add New Files to Folder, Automatically update with Refresh, Power Query Data Import, Power Query CSV File Imports

Id: pMQK1Zcc9jw

Length: 7min 14sec (434 seconds)

Published: Fri Nov 28 2014