digest
Short Summary
Main recommendation: Use Power Query to append (stack) multiple tables into one, choosing among three methods: manually appending tables one by one, using the Excel.CurrentWorkbook function to pull in all Excel tables automatically, or using From Folder to combine CSV or text files stored in a folder. The video shows how to load each method's output back to the worksheet so it updates when source data changes.
Key Points
- Power Query Method 1 (manual append): Convert each range to an Excel table (Ctrl+T), load each one with From Table/Range as "Create Connection" only, then use Get Data > Combine Queries > Append to stack them into a master table.
- Power Query Method 2 (Excel.CurrentWorkbook): This built-in M function automatically returns all Excel tables in the current workbook, so new tables are picked up when you refresh.
- Power Query Method 3 (From Folder): Use this when the data sits in separate CSV or tab-delimited .txt files in one folder; Power Query combines all files in that folder, and newly added files are picked up on refresh.
- The three methods are numbered as presented in the video, and each suits a different data source: tables inside the workbook (manual or Excel.CurrentWorkbook) versus external files (From Folder).
Details Worth Noting
- Concrete instructions:
- Method 1: Select a cell in each table, Data > Get & Transform > From Table/Range; in the Power Query Editor choose Close & Load To > Create Connection only, repeat for each table. Then Get Data > Combine Queries > Append, select the first query and then the second, click OK, name the new query (e.g., "Master Table of Grades"), and Close & Load To > Table on an existing sheet. To add a third table later, load it as a connection, open the master query, use Home > Combine Queries > Append Queries, select the new query, click OK, then Close & Load.
- Method 2: Data > Get Data > Other Sources > Blank Query. Name the query, and in the formula bar type
=Excel.CurrentWorkbook(). Click the filter on the Name column, choose Text Filters > Does Not Equal, and paste the query's own name (to stop the query from pulling itself in). Remove the Name column except the expanded data column, then click the double-arrow to expand the Data column and click OK. Set data types (Decimal Number for numeric columns, Text for text), then Close & Load To > Table on an existing sheet. If a ghost row appears, click Refresh. - Method 3: Put only CSV files in a folder. Data > Get Data > From File > From Folder, browse to the folder, click OK, then Combine > Combine & Transform Data, and set the delimiter to comma. Remove the automatically added source-name step if you don't want it, then Close & Load To > Table on an existing sheet. To add new files, copy them into the folder and Refresh. If the folder path changes, you can edit the query's Source step directly, or use Get Data > Data Source Settings > Change Source.
- Method 2 caveat: Excel.CurrentWorkbook returns the output table too, which would double the rows on refresh unless you filter out the query's own name.
- Method 3 caveat: The folder should contain only CSV files; mixed file types need extra handling not covered in the video.
- Method 1 is simplest for a few tables; Method 2 suits many tables in one workbook; Method 3 suits many external files.
Bottom Line
Choose the append method based on where your data lives: stack in-workbook tables manually or with Excel.CurrentWorkbook, and combine external CSV/text files with From Folder, so that refreshing keeps the master table current.
