9:14
10 - Combine Excel Tables in the Same Workbooks Using Power Query (Formula Method)
TrumpExcel
Overview
This video demonstrates how to combine multiple Excel tables within the same workbook into a single, consolidated table using Power Query's "Excel.CurrentWorkbook" function. It covers creating a blank query, using the formula to list all workbook elements, selecting and combining tables, performing basic transformations like renaming columns and adjusting data types, and importantly, addressing the recursive issue that can arise when refreshing the combined table. The solution involves filtering the list of tables to exclude the newly created combined table, ensuring accurate refreshes.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- Power Query can combine multiple Excel tables into one consolidated table.
- This video focuses on combining tables located within the same workbook.
- The 'Append' method was shown previously; this video introduces an alternative using a formula.
Understanding different methods for combining data allows you to choose the most efficient approach based on where your data is stored.
Combining sales data from four regional tables (East, West, North, South) into a single table.
- Start with a blank query in Power Query Editor.
- Use the formula `Excel.CurrentWorkbook()` in the formula bar to list all tables, named ranges, and connections in the current workbook.
- This formula is case-sensitive and requires exact syntax, as Power Query lacks IntelliSense.
- The function returns a table with columns like 'Name' and 'Content', where 'Content' holds the actual data of each table.
This function provides a dynamic way to access all data elements within your workbook, forming the foundation for combining them.
Typing `Excel.CurrentWorkbook()` and hitting Enter to see a list of tables like 'East_Data', 'West_Data', etc., each with a 'table' link in the 'Content' column.
- Click the double-arrow icon in the 'Content' column header to combine the listed tables.
- Ensure the option to 'Use original column name as prefix' is unchecked to avoid unwanted prefixes like 'Content.'
- The tables are combined into a single table, and the 'Name' column can be transformed to represent the original table names (e.g., removing '_Data').
- Perform data type transformations (e.g., changing 'Any' to 'Date' or 'Whole Number') to ensure data integrity.
This step consolidates disparate data into a usable format, enabling further analysis and reporting.
Clicking the combine button, unchecking the prefix option, then right-clicking the 'Name' column, selecting 'Replace Values' to change 'East_Data' to 'East', and finally setting the 'Date' column's data type to 'Date'.
- A common problem is recursion: the combined table itself gets included in the `Excel.CurrentWorkbook()` list on refresh, leading to duplicated data.
- To prevent this, filter the list generated by `Excel.CurrentWorkbook()` before combining.
- A practical filter is to keep only tables that end with a specific suffix (e.g., '_Data') or start with a specific prefix, effectively excluding the newly created query table.
- This filtering ensures that only the intended source tables are combined during subsequent refreshes.
Preventing recursion is crucial for maintaining data accuracy and ensuring that refreshes produce the correct, non-duplicated results.
Adding a filter step after `Excel.CurrentWorkbook()` to keep only rows where the 'Name' column 'Ends With' '_Data', thus excluding the 'Query1' table from the combination process.
Key takeaways
- Power Query's `Excel.CurrentWorkbook()` function is a powerful tool for accessing all data elements within the current Excel file.
- Combining tables within the same workbook can be efficiently done by listing them with `Excel.CurrentWorkbook()` and then using the combine feature.
- Always check and adjust data types after combining tables to ensure consistency and prevent errors.
- Be aware of and actively prevent recursion by filtering the `Excel.CurrentWorkbook()` output to exclude the target combined table.
- Consistent naming conventions for your source tables can simplify the filtering process.
- Power Query formulas are case-sensitive and require precise syntax.
Key terms
Power QueryExcel.CurrentWorkbook()Blank QueryQuery EditorFormula BarCombine TablesData TypesRecursionRefreshFilter
Test your understanding
- What is the primary purpose of the `Excel.CurrentWorkbook()` function in Power Query?
- How can you prevent the newly created combined table from being included in subsequent refreshes?
- Why is it important to uncheck the 'Use original column name as prefix' option when combining tables?
- What steps should you take to ensure data integrity after combining tables using this method?
- Describe the potential issue of recursion when using `Excel.CurrentWorkbook()` and how to resolve it.