Get Multiple Files Containing Multiple Sheets with Power Query

in this video we're going to look at how you can import multiple files containing multiple sheets with power query even if the data isn't formatted in an Excel table in other words the worst data layout ever let's take a look in this folder I've got three Excel files and you'll notice the file name contains the date this is going to be important because you'll see that the data inside the files doesn't have any date information here I have the January file open you can see it contains eight sheets one for each product category and each sheet contains the sales values by product notice that the data is in formatted in an Excel table nor is it in a named range now my goal is to consolidate the data from each sheet in all three files into a single table so I'm going to copy the folder path from Explorer I'm just going to go back to the folder level and with it selected on the Home tab I'm going to choose copy path and I'll go back to excel I'm going to close this file because I can't get data from an open file and in a new workbook I'm going to go to the data tab I'm going to get data from file from folder and because I copy my folder path I'm just going to ctrl V to paste it in now I need to delete the double quotes that are at the beginning and end of the folder path I'll click OK here I can see a list of the files in that folder and I want to transform the data and this opens the power query editor window and let me bring it across and you can see it contains some metadata about each file now I only want to keep the content column and the name column and the only reason I'm keeping that name column is because it contains the date information so I'm going to right click and remove the other columns now in order to extract the content I need to add a custom column so I may add column tab add custom column and here I'm going to use the Excel workbook function to extract the data from the content column so there's double-clicking to add that to my formula close parentheses on Excel that workbook and click OK and now I can expand the tables by clicking the double-headed arrow at the top and I'll click OK now if we look in the custom kind column we can see a list of all the objects in the files now I only have sheets but if you're importing files that contain tables or named ranges then they'll be listed here as well and it's in this column that you can filter out any objects that you don't want I don't need to filter anything because I want to get all of the sheets so we're going to leave it as is but your data might be slightly different to mine so keep that in mind now if I click in the white space beside the word table in the custom data column we get a little preview at the bottom let me make this bigger so you can see and this gives me a view of the data that's contained in that object so on that sheet we can see the data and we can see in the custom name and the customer item columns this is for the beverages sheet if I click in the next one these are the condiments and confections and so on so this is the data that I'm going to consolidate into one table now before I expand the data in these tables I want to remove all the columns that I don't want in my final data set we're always going to want custom data I also want the custom name because this is my category equally I could choose custom item but custom name will do the job and I want the name the file name columns because this contains my date so with those three selected I'm just holding down shift to select them I'm going to right click and remove other columns so these will be the fields that make up my new file and I'm ready to expand my tables so clicking on the double-headed arrow I'm going to deselect user original column name as prefix and I'll click OK now if I scroll down you can see the data from each of the sheets and each of the files you can see this is all the January file and now on into the February file so scrolling back to the top I want to promote this first row as my header so I can click on this down arrow here and use first row as headers now we have our product our sales amounts beverages well let's rename this one so I'm going to double click to rename it this is the category and this one here is the file name now the headers from the other sheets are still in my dataset we can see one here and if I scroll down there's another one so I want to filter these rows out of my data and we can do this by either selecting the sales column and filtering out all instances of sales or we can use the product column I'm going to go with the product column and filter out product there is there I'm going to deselect it and click ok now at this point you may be finished however in this example remember I don't have any date information it's currently in my file name here but it's not formatted in a date so I need to convert this to a proper date first of all I want to just extract the date and discard the dot xlsx so on the transform tab I'm going to extract the first characters and I want the first seven characters that will give me just the date information next I need to separate the month from the year so we can split the column again on the transform tab we'll split it by delimiter and we'll use the underscore as the delimiter you can see it's automatically detected that I want a custom delimiter and it's populated it for me so I don't need to do anymore other than click ok so now I have my month and my year information all I need to do is join them back together to make a proper date so going to add a column a custom column this will be my date column and I'm going to use the date function to rebuild my date the arguments for the date function are year month and day so if I just move this down a bit we can see file name to contains my year so let's put that in file name one contains my month so I'll put that in and I'm just going to add a one for the first day of each month so all of my dates are going to be the for the first of each month and that's fine for this example I'll click OK and there's my date column I don't need these columns anymore so just selecting them and press the Delete key to get rid of them now all I need to do is tidy up my data types and maybe rearrange the columns so clicking on the icon here I can select the date data type and now all of my dates are proper date data types this one here needs to be a decimal number for the sales amounts and category and product our text already so they're fine I don't need to change anything there the only thing I might want to do is read arrange the order so I can left-click and drag so we go category product sales and then the date lastly let's give this query a better name this name will be the name of the table when you load it into Excel or palette so with my query name updated I'm ready to load my data so on the Home tab close and load close and load to here I get to choose where I want the data placed so the default is a table in the current workbook but we can load it straight to a pivot table report a pivot chart perhaps you only want to create a connection because you want to add it to the data model which is power pivot I'm going to pop it in a table on the existing worksheet which is sheet 1 and I'll click OK so there you can see I've consolidated the data into a single table so we have three files with eight sheets in each file all in one table ready to analyze you can download the file for this lesson from the link here I hope you can make use of this technique if you like this video please give it the thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 163,147

Rating: 4.9776025 out of 5

Keywords: excel power query, power query

Id: sLW3NbeGDy8

Length: 8min 48sec (528 seconds)

Published: Wed Apr 01 2020