How to Import Only One Specific Sheet from Each Workbook in Power Query
Question details
The user needs to combine multiple Excel workbooks using Power Query but only wants to extract data from a specific worksheet (e.g., 'JDE Job Setup') without loading or filtering through all other unrelated sheets.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combining data from a folder containing multiple Excel files with identical structures where only one specific worksheet is relevant.
- Observed behavior
- Power Query currently reads and loads every worksheet from all workbooks before applying filters, causing slow performance and inefficient data processing.
Ensure all your source workbooks are stored in the same folder and that the target worksheet you want to extract has the exact same name across all files.
Use M Code to Extract a Specific Worksheet by Name
Modify your custom column formula to directly navigate to the required worksheet before expanding the data, bypassing unrelated sheets entirely.
When you combine files, expanding the entire workbook first forces Power Query to evaluate all sheets. By specifying the exact sheet name directly in your M code formula, you instruct the query to fetch only the needed data, which significantly improves query performance.
In the Power Query Editor, go to the 'Add Column' tab on the ribbon and click on 'Custom Column'.
In the Custom Column formula box, type the following code: Excel.Workbook(File.Contents([Folder Path] & [Name]), null, true){[Name="JDE Job Setup"]}[Data]. Replace 'JDE Job Setup' with your actual required worksheet name.
Once the custom column is added, remove the original 'Content' column and any other unnecessary metadata columns to clean up your query.
Click the expand icon (two diverging arrows) on your new custom column header to extract the table data exclusively from the targeted worksheet.
Try WPS Office for Fast and Lightweight Spreadsheet Management
Struggling with complex Power Query formulas and heavy file processing? If you need a fast, reliable, and user-friendly tool to handle large Excel files and consolidate data without complex background processes, WPS Office provides an excellent, free alternative to Microsoft Office.
- 1. Download WPS Office: Visit the official WPS Office website to download and install the free suite.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files. Your formatting and formulas will be perfectly retained.
- 3. Manage Data Intuitively: Use the built-in Data tab tools to easily filter, combine, and manage your worksheets without writing complex code.
Frequently Asked Questions
Why is Power Query slow when combining multiple Excel files?
If you expand the entire workbook before filtering by sheet name, Power Query extracts data from every single worksheet first. This consumes heavy memory and processing time. Filtering at the extraction step by specifying the sheet name directly in the formula optimizes this.
Can I extract sheets by their position instead of their name?
Yes. In the M code, you can use an index rather than a specific name. For example, using {0}[Data] at the end of your Excel.Workbook formula will pull the very first sheet in each workbook regardless of its actual name.
What if the sheet name varies slightly across workbooks?
If the worksheet names vary, a strict equality check ([Name="JDE Job Setup"]) will result in an error for non-matching files. You may need to use text functions like Text.Contains in a standard filter step instead, though this might slightly reduce the performance benefit compared to direct extraction.
