I asked him to use Power Query to combine different sheets, but then I realized that for someone new to Power Query, doing this can be tough. So I decided to write this tutorial and show the exact steps to combine multiple sheets into one single table using Power Query. Below a video where I show how to combine data from multiple sheets/tables using Power Query:
Below are written instructions on how to combine multiple sheets (in case you prefer written text over video). Note: Power Query can be used as an add-in in Excel 2010 and 2013, and is an inbuilt feature from Excel 2016 onwards. Based on your version, some images may look different (image captures used in this tutorial are from Excel 2016).
Combine Data from Multiple Worksheets Using Power Query
When combining data from different sheets using Power Query, it’s required to have the data in an Excel Table (or at least in named ranges). If the data is not in an Excel Table, the method shown here would not work. Suppose you have four different sheets – East, West, North, and South. Each of these worksheets has the data in an Excel Table, and the structure of the table is consistent (i.e., the headers are same).
Click here to download the data and follow along. This kind of data is extremely easy to combine using Power Query (which works really well with data in Excel Table). For this technique to work best, it’s better to have names for your Excel Tables (work without it too, but it’s easier to use when the tables are named). I have given the tables the following names: East_Data, West_Data, North_Data, and South_Data. Here are the steps to combine multiple worksheets with Excel Tables using Power Query: The above steps would combine the data from all the worksheets into one single table.
If you look closely, you’ll find the last column (rightmost) has the name of the Excel tables (East_Data, West_Data, North_Data, and South_Data). This is an identifier that tells us which record came from which Excel Table. This is also the reason I said it’s better to have descriptive names for the Excel tables. Here are a few modifications you can do to the combined data in Power Query itself: Now that you have the combined data from all the worksheets in Power Query, you can load it in Excel – as a new table in a new worksheet. To do this. follow the below steps: The above steps would combine data from all the worksheets and give you that combined data in a new worksheet.
One Issue You Must Resolve when Using This Method
In case you have used the above method to combine all the tables in the workbook, you’re likely to face an issue. See the number of rows of the combined data – 1304 (which is right).
Now, if I refresh the query, the number of rows changes to 2607. Refresh again and it will change to 3910.
Here is the problem. Every time you refresh the query, it adds all the records in the original data to the combined data. Let’s understand the cause of this problem and how to correct this. When you refresh a query, it goes back and follows all the steps that we took to combine the data. In the step where we used the formula =Excel.CurrentWorkbook(), it gave us a list of all the tables. This worked fine the first time as there were only four tables. But when you refresh, there are five tables in the workbook – including the new table that Power Query inserted where we have the combined data. So every time you refresh the query, apart from the four Excel Tables that we want to combine, it also adds the existing query table to the resulting data. This is called recursion. Here is how to solve this issue. Once you insert =Excel.CurrentWorkbook() in the Power Query formula bar and hit enter, you get a list of Excel Tables. To make sure you only get to combine the tables from the worksheet, you need to somehow filter only these tables that you want to combine and remove everything else. Here are the steps to make sure you only have the required tables: You may not see any change in the data, but doing this will prevent the resulting table from being added over again when the query is refreshed. Note that in the above steps we have used “_Data” to filter as we named out tables that way. But what if your tables are not named consistently. What if all the table names are random and have nothing in common. Here is the way to solve this – use the ‘does not equal’ filter and enter the name of the Query (which would be ConsolidatedData in our example). This will ensure that everything remains the same and the resulting query table which is created is filtered out. Important Note: In the example used in this tutorial, the headers were same. In case the headers are different, Power Query will combine and create all the columns in the new table. If the data is available for that column, it will be shown, else it will show null.
Combine Data from Multiple Workbooks in Excel (using Power Query). How to Unpivot Data in Excel using Power Query (aka Get & Transform) Get a List of File Names from Folders & Sub-folders (using Power Query) Merge Tables in Excel Using Power Query. How to Compare Two Excel Sheets/Files How to Switch Between Sheets in Excel? (7 Better Ways)
This has been a tremendous help! Although I do have a question, I am trying to combine 4 worksheets into a single table and all three have columns labeled as “Warehouse”, “Count Date” and “ABS ($ VARIANCE”). The information from all 4 sheets is being pulled into my consolidated table with the exception of some of the count dates. When I look back at my query, some of the rows are returning a “null” value for some of the dates, thus leaving the cell blank in my consolidated table. Would you be able to provide insight on how I can correct this issue? Thanks so much Mary From there, I had to go to “EDIT QUERY.” Then, I had to figure out how to delete the columns in the query in order to get the same two columns (“CONTENT” (table) and “Name” (sheet name).” From there, I right clicked on the “double arrow” as explained above. It preloaded all the columns and I hit “OK.” Now I was able to simply “RENAME” each column with the headers I needed from each of the sheets. Then I clicked “Close and Load” and done. Thanks again for this post!
