About this lesson
Wouldn't it be nice if you could append all Tables in the current Excel workbook without having to do each manually? You absolutely can - and this lesson shows how to avoid causing massive errors in the process.
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.Append objects within the current Excel workbook
149.1 KB Append objects within the current Excel workbook - Completed
Append Objects Within the Current Excel Workbook
An overview of appending multiple objects in a single Excel file into one query.
When to use
When you have multiple objects (Tables, Named Ranges or Dynamic Named Ranges) in an Excel file that you would like to combine into a single table.
- Create a new query --> From Other Sources --> Blank Query
- Enter the following formula in the formula bar: =Excel.CurrentWorkbook()
- Press Enter and a table of available objects is created
Building the Query
- Filter out any objects (tables or ranges) which you do NOT wish to include in the append
- Expand the [Content] column to append the data in each remaining table
- Apply further transformations as required
- Set data type for each column
- Rename the query
- IMPORTANT! Ensure that your steps to date have filtered out THIS query in some way
- Close & Load the query
Potential for issues
- When loaded, the query will refresh from the list of tables, which now includes your output!
- You have a circular reference if the table loads with errors and the error count increases by the number of table rows at each refresh
Fixing a circular reference
- Return to the Query Editor and go to Home --> Refresh Preview
- Insert a step to filter out the newly created query
Hints & tips
- If the formula bar is not present in the Query Editor, you can enable it from the View tab
- It is a good idea to come up with logic that prevents newly added tables from being included
- This logic will be custom for each solution but suggestions include:
- Trigger errors intentionally and filter out those rows
- Set custom filter to “Include tables that begin with” rather than excluding named objects
Lesson notes are only available for subscribers.