How to Combine Excel Tables or Worksheets with Power Query

hey there it's John from Excel campus and in this video I'm going to explain how to combine Excel tables with power query and we're going to look at how to fully automate this process which will save you a ton of time in the future so for this particular example we have Excel tables on each sheet within this workbook each table contains some order data and we want to combine all this together - or stack it on top of each other to create one long table this is called an append in power query so there are some prerequisites here before we do the setup work and one of those prerequisites is that each of these sheets needs to contain an Excel table or each data set needs to contain an Excel table so you can see on this sheet here for Andrew this is not yet formatted as an Excel table so to do that we're just going to select any cell inside the data range here go to the Home tab and then format as table and just select one of these styles so click that on this pop up here we want to make sure it's this says my table has headers checkbox is checked and then we'll click OK so that'll insert a table if you haven't used tables yet I do have another video that explains how to use tables in more detail I should also mention that I'll make this file that I'm using available for free download and I'll put a link to this in the description below this video so you can download it and follow along so once all of your sheets have tables the next prerequisite is that all of the column headers within each table will need to be the same they don't need to be in the same order but they do need to have the same name or the same text within each of those column headers so if they don't for some reason you can copy the column headers on the first sheet and then paste those select multiple sheets even and just paste those column headers so that they're all the same on every sheet okay so next we're going to do the set up work in power query and this is work that we only need to do one time and once we do this we will fully automate this process and at the end of the video I'll explain how to update and refresh the data so we can just select one sheet here just make sure you have one sheet selected then we're going to go to the data tab on the ribbon and if you're in Excel 2013 or Excel 2010 this will say power query power query was a free add-in for those versions of Excel I'll put a link to an article in the description below this video that explains how to install power query for those older versions of Excel so we're going to go to the data tab here I'm going to select a cell inside the table and then we're going to choose from table or range and that'll bring up the power query editor and here we will see a preview of our data so for this we don't need to do anything yet we don't need to make any transformations or clean up this data yet all we really need to do is create a connection to this table so in order to do that we're going to go to the home tab and on the close and load button here this is a split button we're going to click the bottom half of this button and then choose close and load - and that will bring up this import data window and here we want to choose only create connection so just create the connection otherwise power query is going to create a new sheet with an output table and we don't need that yet so we're just going to create connection and hit OK and then you'll see over here on the queries and connections pane this will appear over here on the right side and we'll see our new connection now at this point here we need to do that same setup work for each table in the workbook so we'll go to the next one do the same thing from select a cell here from table and then close and load and create the connection only so you'll do that for each sheet or each table in the workbook now if your workbook has a lot of sheets that can take you some time so I've created a macro that I'll do this for you and I put it here on my macros tab I stored it in my personal macro workbook and put a button up here for this I'll share this in a separate video on how to do this yourself and share the macro code as well in a separate video but essentially we're just going to run this macro so I'll click that button there this will just ask us if we want to run the macro to create the connections hit yes and then that will create all of the connections in the workbook takes about two seconds to do that depending on how many tables you have and you can see over here on the right side we have all of our connections created so that'll save us some time and now that we have all the connections we can combine them or append them in power query so we're going to go to the data tab on the ribbon on the get data drop-down we're going to go to combine queries and then append so again this append is combining the queries or stacking them on top of each other and that will open up this append window and here we want to choose three or more tables because we have more than two and over here on the left side we'll see our available tables so one thing you can do here is just select the first one hold shift and then select the last one that'll select all of them now if you have additional tables in this workbook or additional queries that you do not want to append you would just not select those and then once you have the ones you want selected you can just click Add if you accidentally add one that you don't want you can click here and hit the X to unselect it this is that one we added I forgot to name the table but we'll leave it in as is for now so we'll go ahead and hit OK that will again load up the query editor and append or combine all of our tables together and we can see that here if we scroll over to the right side to our last table with the rep name as we start scrolling down we can see the wrap name changes so this is the data from the different tables in the workbook so it's all been combined and at this point you can do additional cleanup work so for example if you maybe wanted to filter out some rows maybe with our payment type when our payment types a blank or no we don't want rows for that so we could filter those out just remove all of those and do additional transformations and cleanup work here with power query and you'll also want to make sure your data types are correct so if you have any columns with dates or numbers this an example here we'll want to change this to a date datatype so when we output this that'll be a date in Excel so at this point our setup work is complete and what we could do is go to the Home tab and we can click the top half of the clothes and load button that's going to add a new sheet to the workbook and output our query table right here with all of our data combined so now let's take a look at updating and refreshing the data as I mentioned before we've fully automated this process to combine these files if the data on any of the sheets change let's go over to this sheet for mariah here let's just say this order number changes here or maybe we even get some new rows down at the bottom I'm just gonna copy two rows and paste them at the bottom here I'll just change these numbers here to make this stand out a bit something like that hit enter and then now if we want to see that data in our query all we need to do is go back to that output table and anywhere on the table we can right click it and click refresh keyboard shortcut is alt f5 so when we do that the query reruns completely and outputs the data to the table so we can see that data here for the data we change we scroll down a bit we can now see the new data here as well and you'll notice we don't have the eight eight eight one and that's because that row was filtered out because it had a null value in the payment type so that's why we're not seeing it in the output table so the point here is that power query is going to completely replace the data any existing data in the table when it does a refresh so we're going to just completely replace that data so if you do make changes to the data in the output table that those changes will be overridden once we refresh this table and then one other maintenance piece you might need to do is adding new tables so let's say we get another table here we'll just change or make a duplicate copy hold ctrl drag to the right and so we have a duplicate copy of this sheet I'm also going to change the rap name here just so we can see a difference in our data just copy that down so now we have a new table the first thing we'll have to do is create a connection again for this table so we can go to data we also rerun the macro the macro will create any new tables for us or new connections with new tables for us as well and not duplicate them but what we'll do here is just let that load up and then we'll click the top or I'm sorry the bottom half the split button close and load - and again only create a connection there and we'll hit OK so that's created a new query here you can see it's now table nancy 15 and what we'll do is double click that to go back into the power query editor in our append query and here we need to include that additional table so on the source step here we can click this little gear icon right next to the source step if for some reason you don't see the query settings pane over here on the right side this can easily get turn toggled off or closed go to the View tab and click the query settings button this is a toggle that toggles it on and off so we'll go to source click the gear icon and that will bring up that original window where we selected our tables so here we have the new one for Nancy I'll just select it hit add that'll put it at the bottom you can also reorder here if you wanted to select that and move Nancy up to a different spot you can totally do that as well and hit OK and then that will just refresh the query and include our new table so again we'll go home hit the top half of the clothes and load button once we've already hit that close and load button for the first time power query will not create a new sheet it's just going to refresh the query on this existing sheet here and now we'll have our data for Nancy so if we hit the filter drop down here we can see we have Nancy and then Nancy free Haffer with her last name there so we have both of those tables in our output so it's a little bit of maintenance there if you're adding new sheets frequently there might be some maintenance there otherwise you have a fully automated process and again as the data changes or if you add or delete rows within any of your source tables all you have to do is refresh the query to see the results here and then of course with this table you can use this as a source of a pivot table or formulas to do reporting and analysis I have a whole nother video series on pivot tables and dashboards I'll put a link to that in the description below this video as well definitely check that out to learn how to bring your data alive and make very cool charts and interactive dashboards so I hope this has been helpful for you and it saves you a lot of time with doing kind of boring data cleanup work at your job of course if you have any questions please leave a comment below and if you're putting this technique to good use let us know in the comments below as well how much time it's saving you so thanks again for watching have a great day and I'll see you in the next video [Applause]

Channel: Excel Campus - Jon

Views: 106,749

Rating: 4.9452343 out of 5

Keywords: excel, excel campus

Id: HcfKOusOJhA

Length: 11min 56sec (716 seconds)

Published: Thu May 21 2020