Excel Magic Trick 1331: Import Multiple Excel Files & Sheets into Excel: Power Query Get & Transform

welcome to Excel magic trick number 1331 hey if you want to download this Excel workbook excel metric 1331 part 1 start or the part 2 file so you can follow along click on the link below the video wow we have an amazing video here follow up on our last video but in this video we want to see how to import multiple Excel files with multiple Excel sheets into Excel and we're going to use power query also known as get and transform now if we're importing multiple excel files that have multiple tables there's actually a few ways we can do this and we're going to look at two of them we're going to look at a pen and merge now in our first example we will have these 5 CD sales files and we need to import all of them and append them one on top of each other into a single table and then for this example we'll actually make this pivot table now I'm going to go to part 2 file and in this example we have two files yes but one of them looks like this it is actually a lookup table here's our products here's our price and down here the second file of sales this is what it looks like it has date product units and discount but no price so we need to import both of these tables and actually merge them into a single table adding this price column now the word merge is what we see in power query but if you know how to do vlookup to add an extra column or create a power pivot relationship to get price so we can calculate total revenue then you have the idea of how merge will work now let's go over to our first file part 1 let's go look at these files now when you download the files you have to download this folder and unzip it called one three three one part one source data and here are our files I actually threw a bunch of problems in here so that when we get all these files into power query we'll see how to deal with potential pitfalls the files we really want are Oakland Portland San Francisco Seattle and Tacoma now one thing to notice and we'll deal with this over in PowerPoint sometimes you have a dot xlsx sometimes we have a dot xlsm and look at that someone actually capitalized the extension not only that but I threw a couple other files that are not Excel files in here just to see how we can filter out because we're going to use an amazing feature where we actually point power query to the folder and it will automatically bring everything into power query instead of individually five times having to pick out individual files now we need to go look at these files and notice that the city name is actually in the file and power query will allow us to take the city name and use it inside the data set as an extra column called cities so for the resultant table each one of the records in each one of these files will be coded with the city name now let's go look at this oakland file double-click open it up oh look down here we have individual tabs Fran gab papi those are the sales rep so not only we're going to need to get the city name from the file but we're going to need to for every transaction here we need an extra column called sales rep and we need to get the information from the sheet tab Fran into this extra column power query will make that easy now in this excel workbook there is an extra sheet that was not named now I threw this in there just so we can see how to filter it out not only that but I added a bunch of extra objects that will be imported when we import all these files and we're going to need to filter them out let's go look at the define name drop-down oh there's a print area just like we saw in our last video there's an actual defined name there's also a table so we'll have to filter those objects out all right so here's our data set date product unit sales then we need to get the sales rep information from the sheet tab and city from the file name I'm going to close this file alright we're back in our part 1 file on the sheet product report now in Excel 2016 power query is on the data tab get and transform group is power query in Excel 2013 or 10 you have to download it and install it as a new tab all right let's do this we're going to go to new query from file and that from file button is just on the power query tab in earlier versions then we go down to this amazing feature from folder folder dialog box wants us to browse and there it is the folder we downloaded and unzipped now I'm telling power query to point to this folder any files and any subfolders with files all of those files will be imported click OK click ok this is an intermediate step if you have the latest version of power query we want to click Edit not load we want to edit here's our power query editor first thing I'm going to do is come over and name this this is going to be our all-city tables and enter notice that's a property we don't get an extra step when we rename it now what a power query do these are all the files in that folder with information about the files a lot of things we do not need this has the content of each file here's the name which we're going to use and we want to start with extension so I'm going to click on extension now two videos ago I showed you an important ass pect of filtering with power query power query filter is case-sensitive now let's just imagine for a second we didn't know that we thought we were over in Excel what we really need to do in this column is we only want file extensions that have dot XLS because we want the M and the X so I'm going to come up here and do a contains filter text filter contains and I'm going to type dot XLS and when I click OK whoa wait a second it filtered out the capital ones and that's not what I want so I'm going to come over here to apply it steps and click the red X what we want to do first is we want to make all of these lowercase transform over in the text we go to format and please give us lowercase you can also right click transform lowercase boom there we go we never had to worry about that over in Excel but here in power query we have to be keenly aware that filters are case sensitive now I'm going to say text filters contains dot XLS click OK just like that we have filtered out any files that don't contain dot XLS now interestingly enough in this filter we use wood imported XLS or a dot XLS B however both of those file types are more difficult to deal with when you're gathering up files from a folder and I'll point something out later in the video about that so in general we want to stick with the dot XLS X or dot XLS M now we need to get rid of everything after the dot here so we have our actual city names we can use in our resultant tables so I'm going to come up to transform split column by a delimiter our delimiter that will be a marker in this text that'll split in for us it'll be dot click the drop-down custom I'm going to say dot and actually I'm going to be careful here I'm going to say at the rightmost delimiter in case someone had a file name with a dot our cities are not going to have it but it pays to be careful when I click OK sure enough it split it now look over here it adds this extra step it changed the type I don't really need that now it's perfectly alright to leave it in there but I'm going to X that out and get rid of it now we don't need any of these columns here all I want is the city name and the content so I'm going to click on content then I'm going to hold shift and click on name right click remove other columns now these are Excel files not txt files like CSV or txt we cannot click that button to expand that double downward pointing arrow is for text files we actually are going to have to add an extra column and use Excel dot workbook function to get at all of the objects in these files so I come up to add column add custom column I'm going to come up and name this get excel objects now you know you don't have to name columns lots of people even custom but I always like to give everything a smart name you never know when that smart name is going to help and we're going to use excel dot workbook function now last video we saw excel dot current workbook excel workbook will get these objects from these external files now we want to double click on content that means in the content column for each file it will get the objects and we want to type a comma first argument is the content second argument is going to be do we want to promote headers and we do type true now that's a much different true than in Excel those all have to be lowercase letters for Excel workbook to understand that we should promote headers that means when we append all these tables and stack them one on top of each other there will be a single listing of field names at the top the remaining field names and all the tables below will not have that field name included as a record in the data set all right we have our formula I'm going to click OK there's our column we can come to content right click remove now we have our sideways double pointing arrows which will allow us to expand and get to our data I'm going to click it I definitely want to uncheck use original column name as prefix we do not want these names we want all these names when I click OK we have a bunch of new columns there's our city name this name gives us the name of the object this column kind tells us what type of object is it a sheet is it a table is it a defined name we also have hidden and notice there was something filtered in the Oakland file that created a define name that was hidden all the rest of the objects are not hidden there's our data and kind is similar to the name with a slight difference you can see like the filter name here has sheet name now we're going to use the kind column and as we mentioned last video excel current workbook didn't have this so we actually had to add an extra column and do some fancy stuff but here I can simply click the filter text filters and I want only objects that equals sheet capital Sh eet click OK and just like that now I have all of the sheets now we do have a problem here because our name column has our sales rep names there's a sheet left over so we're going to filter out any sheets that have the default name sheet click the drop down text filters we want does not begin with sheet I guess this assumes that we don't have any sales rep that have the name sheet if we did we'd have to know that and do something different click ok that is amazing there's our city names from the file name there's our sales rep names from the sheet names there's our data now we don't need any of these columns and watch this I need to highlight all these and then right-click remove other columns but I'm going to highlight them in a certain order and here's why I'm going to click on data first then I'm going to hold the ctrl key and I'm clicking on name second and still holding ctrl sales rep name column third by highlighting it one two three when I right-click remove other columns instantly it removes the other columns but it puts it in the exact order in which I selected the columns that's pretty cool and that's the order that I wanted them now we can finally click on the expand button to get to our transactional records I definitely want to uncheck this because I want to use these names those are the field names from our tables when I click OK that is amazing our transactional data and then repeated from our file name and sheet name all the proper City and sales rep now we need to go through each one click on date home or transform to get at data type I'm going to say data type date product by the way we can also use right click Change type and this is going to be text right click change type whole numbers sales whichever method you like decimal text had already got it didn't get text there so I'm going to say text and there is our table ready to go we have the her name there's all our memorized steps let's close close and low two and I'm going to put this as a new table on a new worksheet click OK there is our table over here we can see our query 50,000 plus records also our table has been automatically named design table tools properties and there is the name so the name of the resultant table and the query are the same now I want to create a pivot table from this imported appended data set insert pivot table or we can use the keyboard Alt + V I'm going to accept the default on a new worksheet and instead of clicking ok it's the highlighted button so I hit enter now I want to drag date down to the rows remember these are individual transaction dates when I drop it down here I'm using Excel 2016 it automatically groups now I do not want quarters and I do not want date all I want is years I'm going to put years all the way up into columns drag product down to rows our pivot table is starting to emerge sales rep and look at that I completely violated proper naming conventions well watch this right in the middle of the pivot table I'm coming over to the workbook queries right click edit I am back to my editing window double click the field name we're going to call this of course city enter double click we're going to call this sales rep enter now I've already closed and loaded this to a sheet so I simply have to click this button and it will update I'm going back over to my sheet which I see I have not named yet double click and I'm going to call this PT enter now I'm looking at my field as what they're not there I have to come over when you use power query to import in a table and you have a pivot table you have to refresh twice once in the table and then once in the pivot table right click refresh and look at that that's pretty amazing to be able to go back into power query and update all right I'm dragging sales rep down below product now sales down to values I immediately want to come somewhere in the pivot table values area and right click I do not want to use format cells I want to use number formatting to actually add number formatting to the field sales so I select number use a separator 0 decimals click OK immediately I'm going to come up and for some of sales I'm going to add in parentheses the unit enter that way I don't clutter up my report row labels and column labels are terrible so design layout report layouts show in tabular now watch this I'm actually going to add a slicer here analyze filter insert slicer and I'm an out of slicer for criteria that I already have in my pivot table I want products click OK now why in the world would I do that there's all of our products well here's why I'm going to select Ozzy round Carlota and instantly it is filtered and I have the Carlota listed over here including the Carlota total if I want to see them all I can see them all select Balon now I want to go back and there's another file that you can download let's go over and look in Windows Explorer now you can download this as one of the files and if I double click and open it it's just the sales rep sheet with all the sales for the city of San Jose so I'm going to close this file and I'm going to drag it in to our folder remember power queries pointing towards that folder I double click and open it boom there they all are and there is San Jose now when I go back to my sheet with the report I come over to my workbook where's by the way if that's not open dad I show queries but I want to right click refresh and there it is new records now I go to my pivot table right click refresh and just like that I have my updated report all right so our first example of taking multiple sheets from multiple Excel files went like this we had our files and we did append now I'm going to close part one workbook alright we're now on the part two file and here's our goal we still have multiple tables we need to import but this time instead of appending we need to merge here's the lookup table we need all the proper prices for each one of the products actually as a new column in this sales table this is what the end result is going to look like actually this is not what the end result is going to look like we're going to add an extra column with a formula that calculates our sales because we do not need units discount or price in our final table alright so let's see how to import these two tables and merge them data new query from file and we're actually going to import the files one at a time and then merge them so I'm going to say from file from workbook navigate to our source folder and I'm going to select sales first we're importing a single excel file we get our navigator window that will list all of our objects we want only the sheet sales there's our preview I'm going to click Edit sales table is an okay name because it's going to be loaded as connection it will not be our final table there are some steps it did and it look like it got pretty much everything correct D text for product it got units which 1 2 3 means a whole number you can see that up here and and 1.2 is the icon that means decimal got it pretty much correct come up to close and low close and low too we want to select only create a connection and load we can see over here it's loaded now we go get our second table new query from file from workbook double click product prices now notice there's a few objects here I simply want the 1 sheet with the 1 table I'm going to click Edit prices is a fine name there's our steps it looked like it got the first column of our lookup table with a unique list of products it got text right it got data type decimal for our price now we can come up to close and load close and load to only a connection click load here's our two tables now we come back up to new query combine queries and we want to merge we select our sales table here's our product column if we were doing vlookup vlookup would look up each one of these items notice this column is allowed to have repeats now we come down and find our lookup table or if we're using power pivot this would be our dimension table there's our prices there is our unique list vlookup would look this item up find a match and then return the price creating an extra column if we are in PowerPivot we would have a one-to-many relationship that would allow us to use the price in this table now join Khan we want enter only matching rows now I click OK there's our sales table and here's a new column we're going to select expand I do not want all the columns I only want the price column and I want to make sure and unselect this I do not want the column name from over here I just want price click OK and there is our V look up helper column now I actually don't want any three of these columns so I am going to calculate in an extra column here our sales the formula is going to be units times price times one minus the discount let's add an extra column add custom column we're going to call this revenue now look at this I have my fields over here I can double click units hey units times and double click price but we have a discount and that discount is something that should be subtracted from the total amount so times and then in parentheses one that represents the full price minus whatever the discount is close parenthesis now we have a bunch of decimals and sometimes extraneous decimals past the penny position so we need to round this to get exact penny amounts now we're going to make an error here but this will be a useful trick for learning new things about power Corey I'm going to assume that the name of the function is round and I'm even going to assume that I have the case right now there it is I come to the end comma two because I want to round to the penny closed parenthesis look at this it says no syntax errors have been detected so I'm going to say okay and look at that the name round was not recognized well I'm going to go right out to Google and I'm and a type power query round function and enter and there it is here's the one from Microsoft oh look at that I bet you I should put number dot round if I click on this here it is number dot round now here's the description of the arguments the first one is simply the number the second one is the number of digits and there's this third argument here here's the description of the wave describes it here is what happens when the last digit is five and there's some auctions and they're listed here now the amazing thing about the power query number dot round is that if we leave this last argument empty it assumes this last one which is banker's round which is more accurate over large sets of numbers than our standard rounding that we use in school not only that but the bankers rounding algorithm is the same one that's used by Excel VBA and access all right let's go back to our Excel workbook now I've added this column I'm simply going to double click to edit this and right before here I'm going to type number dot round and now when I click OK there it is now I'm going to select these three columns units hold shift select price right click remove and there's our final columns so I say almost violated our most important naming convention always name things smartly this will be the revenue table and enter now we want to add a data type data type decimal now up to close and load close and load actually if I click close and load right here it automatically puts it on a new sheet there's our table there's our final query double-click and I'm going to call this something like a revenue table and enter now I'm going to click in a single cell and make a quick pivot table envy I definitely want to put this on a different sheet the location sales report a one click OK date I'm going to drag down two rows I'm going to drag the individual date of product down below month's revenue down to values right click expand and collapse expand entire field right click somewhere in values number formatting I'm going to say currency 0 decimal click ok up to design report layouts show in tabular and that's pretty amazing we created pivot table from this merged table from to external Excel files wow that was a lot of amazing fun with importing multiple Excel files using power query we saw how to merge two tables into a single table and we also saw how to take multiple Excel files and append them all using the from folder feature alright that was a lot of fun with power query we'll see you next video

Channel: ExcelIsFun

Views: 190,769

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Excel Magic Trick, Import Multiple Excel Files, Import Multiple Excel Worksheets Sheets into Excel, Power Query, Get & Transform, Append Multiple Workbooks, Merge Multiple Workbooks, Append Tables, Merge Tables, How to create a PivotTable from two separate data sources, How to create a PivotTable from two separate excel workbooks, From Folder, From File

Id: ldoQws7Zbx8

Length: 29min 47sec (1787 seconds)

Published: Sun Oct 16 2016