MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

TL;DR

Learn how to use the Excel.CurrentWorkbook function in Power Query to append all Excel tables in the current workbook into a single table.

Transcript

[MUSIC PLAYING] Welcome to MSPTDA video number five. And in this video, we're going to talk about power query's amazing Excel.CurrentWorkbook function so we can take all of the Excel tables in the current workbook and append them into a single table. The Power Query function we're going to look at, Excel.CurrentWorkbook. If you go to Microsoft Help... Read More

Key Insights

• 🔨 The Excel.CurrentWorkbook function in Power Query is a powerful tool for retrieving Excel tables and other objects from the current workbook.
• 🧡 Filtering out unwanted objects such as defined names and print ranges can help ensure that only tables are appended.
• 🚰 Loading tables to the pivot table cache avoids the recursion problem and allows for easy creation of pivot tables.
• 🤨 Paying attention to names and renaming columns can help prevent confusion and duplicate rows in the final output.

Questions & Answers

Q: What does the Excel.CurrentWorkbook function in Power Query do?

The Excel.CurrentWorkbook function retrieves all Excel tables in the current workbook, along with other objects such as defined names, print ranges, and filtered ranges.

Q: How can I append all Excel tables to a worksheet using Power Query?

You can use the Excel.CurrentWorkbook function in Power Query to retrieve all tables and then expand them into a single table. You can then load this table into a new worksheet.

Q: What is the recursion problem when using Excel.CurrentWorkbook?

The recursion problem occurs when the Excel table that is created from the Power Query result is also imported back into the Power Query, causing duplicate rows. This can be solved by filtering out the query name.

Q: How can I append all Excel tables to the pivot table cache using Power Query?

You can load the table created from the Excel.CurrentWorkbook function directly into the pivot table cache. This allows you to create a pivot table from all the tables in the workbook without the recursion problem.

Key Insights:

• The Excel.CurrentWorkbook function in Power Query is a powerful tool for retrieving Excel tables and other objects from the current workbook.
• Filtering out unwanted objects such as defined names and print ranges can help ensure that only tables are appended.
• Loading tables to the pivot table cache avoids the recursion problem and allows for easy creation of pivot tables.
• Paying attention to names and renaming columns can help prevent confusion and duplicate rows in the final output.
• Power Query provides a wide range of functions for manipulating and transforming data.

Summary & Key Takeaways

• The Excel.CurrentWorkbook function in Power Query can be used to retrieve all Excel tables in the current workbook.

• It also returns other objects such as defined names, print ranges, and filtered ranges.

• Examples are provided on how to append tables to a worksheet, the pivot table cache, and how to handle defined names and other objects.