digest
Introduction
In this video we want to see how to append or combine different tables using Power Query, and we're going to see three methods. We'll see the manual method where we append tables one by one. We'll see how to use the Excel.CurrentWorkbook function, which is a Power Query M-code function that automatically grabs all the Excel tables in the current workbook. And then we'll see how to use the From Folder feature when the table text files are all separate files.
Excel Tables to use Power Query
In order to use Power Query in the Data ribbon tab and take data from the Excel workbook, you have to have an Excel table. I've already converted all three tables to Excel tables. It's easy: just click in a single cell and use the keyboard shortcut Ctrl+T.
Method1: Power Query Append Feature
For the manual method, you select a single cell, go up to Get & Transform, and we have to use the From Table/Range button to bring the table into Power Query before we append. So I click this, which opens the Power Query Editor. That name is fine, and the steps it did are fine. We come up to Close & Load, Close & Load To, and we want to select only "Create Connection," which means we're loading that data into the Power Query Editor so we can use it in our append. Click OK.
We do the same thing for table number two: From Table/Range, Close & Load, Close & Load To, only Create Connection, click OK. Now we have two tables in Power Query. Yes, we want a third one, but we'll see how to add that one later.
Now that we have both tables inside the Power Query Editor, we come up to Get Data, Combine Queries, and there it is. The picture says we're putting one on top of the other using Append. This Append window from the drop-down only shows queries, so we'll select the Grade One query, and then for the second table we'll select the Grade Two query. Then we click OK.
This is a third query, and we're going to give it a descriptive name, something like "Master Table of Grades." Now we click Close & Load, Close & Load To, and we want to load it now as a table on the existing sheet. Very carefully, I'm going to click on Method 1 and cell B2, there it is. Click OK. We can see the query over here, but it wasn't polite, so I'm going to click on Method 1 and there's our table.
Now when we get more tables later, here's Grade Table Three. Select a single cell, From Table/Range, and we'll load it as a connection only. Now we want to edit our query, so we double-click to open it. We can see our query name here on the left. You can always open the Queries pane and see all the different queries we have in this Excel workbook. We have our current query, and we want to go up to the Home ribbon tab, Combine Queries. I want to append to the bottom of this one, so I use Append Queries and select the new table, Grade Three. Click OK.
Now I can just click Close & Load, because this has already been loaded, and sure enough, we have updated this Master Grade table. I'm going to press Ctrl+S, and we'll come back to this one later.
Method2: Excel.CurrentWorkbook function to combine all Excel Tables in current workbook
For example two, we're going to use a different workbook. There's the name of this Excel workbook, and we already have our tables. We'll go over to Method 2, and there's a built-in function called Excel.CurrentWorkbook, which automatically brings in all Excel tables in the current workbook.
There's a strange twist to this function. After we use it, it's going to load the table onto this sheet, Method 2, as a new Excel table. So if that function is really programmed to automatically get all Excel tables in the current workbook, it's going to try to get itself and give us double the records when we refresh. There's an easy fix to that, but you just have to remember to do it.
Data ribbon tab, Get Data, Other Sources, and we're going to create a blank query. Here's the editor, and we definitely want to name this query. I named this "Master Grade Table ECW" for Excel.CurrentWorkbook, and that's the name of this query that we haven't created yet. But when we load this as a table, that's also going to be the name of the Excel table. So I'm going to use this name again: highlight, copy, and enter.
Now there's a Source step here, but there's no code, and that's because we get to write a formula in Power Query. This is the official M-code language. We type an equal sign and we can type the name of the function, and I see it right there: Excel.CurrentWorkbook. This is an argument-list function, so I can simply open and close parentheses. It's just programmed to always go and get all the Excel tables and other objects like defined names, print areas, and things like that.
When we use this function, we want to make sure that we only have Excel tables in our workbook, and we do. When I hit Enter, it went and got a column with the actual tables, which is different than Excel. In Excel we can't have a whole table in a cell like we can in Power Query. That's the name of the table, and this is where we're going to create a filter to block this exported table from ever becoming part of the table.
We click the filter drop-down, Text Filters, Does Not Equal, and then we paste. You have to remember to do this step even though it seems strange that they would program it that way, but that's how we get the benefits of the Excel.CurrentWorkbook function. Now I'm going to click OK. It doesn't do anything yet because we haven't loaded it.
Now we don't need this column, because we only want the tables to append one on top of each other. Watch this: we have used that step to filter, but when we come up to the top of the column and right-click Remove, it adds a step here, but the filtered rows step is still there and will always be part of this query. Now we've removed the rows, and we want to click this double-sided arrow to combine. I don't want to use the original column name as a prefix to these names; I just want these names. Now I click OK, and there's the append with all our Excel tables that we have currently or in the future, except for the query output.
In Power Query, if we're going to use this in a pivot table or something like that, this actually has to be a number. You'll see at the top that the data type icon says "ABC123," which means Power Query doesn't know what data type this is. We have to give columns data types. We're going to give this a data type of Decimal Number, and we'll give this a data type of Text. Now we can Close & Load, Close & Load To, as a table on the existing worksheet, B3. Click OK.
Notice there's this little ghost row right here. That's because it thinks it's still looking at itself. If we click Refresh, it'll go away, because we very carefully took that query name and told it to filter out that table name. You can try adding a new table, converting it to a table, and refreshing, and you'll see that it works perfectly.
Method 3: Append csv files from a folder using From Folder feature.
Method number three is called the From Folder option for appending tables. You use this option when you have files that contain tables. We have text files, either CSV (comma-separated values) or .txt files, which is a tab-delimited file. If we have a bunch of files and we need to append them, that's when we use this method.
When you download this folder, it'll be zipped. When you unzip it, it will have the Start folder. That's where we're going to tell Power Query to go and get all the files, and there's an extra file on the outside that we'll use later. Very importantly, when we look in our Start folder, we're going to assume that we only have CSV files. If we had other types of files in here, there's an easy enough way to deal with that in Power Query, but it's more complicated than what we're going to see. It's a safe assumption; all we're going to put in here is CSV.
On the sheet Method 3, we'll go up to Get Data, From File, From Folder. We need to give it the folder path. Click Browse, wherever you have downloaded it, there's the Start folder, click Open. That's the folder path. If you move that folder, we have to edit this later, which is easy enough to do.
Click OK. There's a preview of the two files, and we're going to use the Combine button. When I click the drop-down, Combine & Load would just automatically do it to the worksheet. Combine & Load To would give us the option, but usually we want to click Combine & Transform Data so we can look inside the Power Query Editor to see if everything's okay. So I'm going to select that option. It wants to know what the delimiter is; we have comma-delimited data. Click OK.
Now we want to name this query. I gave it a good name. The From Folder process created a bunch of steps and a bunch of queries over here. I'm going to close the query window. One thing the automatic process did that we don't want is that it took the name of the file. Change Data Type is the last step. I want to add a step, so I right-click Remove, and there's the appended table with data types and everything. Close & Load, Close & Load To, table on the existing sheet, click OK.
That's pretty amazing, because now that query and that table are all looking in that folder. If we add tables or delete tables later, everything will update. Let's go test it. Grade Three: I'm going to copy it, and inside of Start I'm going to press Ctrl+V. Now I can right-click Refresh.
If that folder path changes, you can edit the query. Double-click, and there's the Source step. Click the gear icon and you can edit it there. Otherwise, a probably easier way is Get Data, Data Source Settings, select the source, and use the Change Source button.
So we saw three ways to append. Use Get Data, From File, From Folder when the files are coming externally into Excel. We can use the Excel.CurrentWorkbook function if all the Excel tables are in the current workbook; just remember to filter that name out, like we did in this step. And then we started with the manual method of using From Table/Range and Get Data, Combine, Append.
Summary, Closing and Video Links
If you like that video, be sure to click that thumbs up, leave a comment, and subscribe, because there's always lots more videos to come from ExcelIsFun. And be sure to check out the two other append videos I just did: one with INDEX, LET, and LAMBDA, and one with the FILTER and XML functions.