How to Apply Power Query Transformations to Every Worksheet
Question details
The user needs a method to combine data from numerous worksheets (e.g., over 100) and efficiently apply the exact same Power Query transformations to all of them at once.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Consolidating data from over 100 worksheets into a single master table while applying uniform data cleaning and transformation rules.
- Observed behavior
- Users require a way to avoid repeating steps for every single sheet. Using the expand data method merges the sheets successfully, but headers from each sheet may repeat in the rows if the source data is not formatted properly.
Before importing your data into Power Query, format the data ranges in each of your source worksheets as a proper Excel Table (Ctrl+T). This prevents the column headers of each individual sheet from appearing as repeating data rows in your final combined dataset.
Use the Expand Data Column Method
This is the most straightforward method to combine multiple sheets from a workbook and apply uniform transformations to the unified dataset.
Instead of connecting to each worksheet individually, you can connect to the entire workbook at the folder level. By expanding the unified data column, you only have to apply your transformation steps once.
Open a blank workbook, navigate to the 'Data' tab, select 'Get Data' > 'From File' > 'From Workbook', and open your target file.
In the Navigator window, select the folder icon representing the entire workbook instead of clicking the individual sheet names, then click 'Transform Data'.
In the Power Query Editor, locate the 'Name' or 'Item' column. Click the filter drop-down to uncheck any worksheets you do not want to include in the consolidation.
Select both the 'Name' column (to keep sheet identifiers) and the 'Data' column. Right-click the header of either selected column and choose 'Remove Other Columns'.
Click the expand icon (two diverging arrows) located in the upper-right corner of the 'Data' column header. Uncheck 'Use original column name as prefix' and click OK.
Now that all worksheet data is combined into a single table, apply your desired transformations (such as changing data types, filtering rows, or adding conditional columns), then click 'Close & Load'.
Create a Custom Function Using Parameters
Use this method if you need to pass specific parameters or require more complex transformations to be applied individually to each sheet before they are combined.
Consolidate Data Easily with WPS Office
While advanced Power Query functions are native to Microsoft Excel, WPS Spreadsheets offers highly compatible, lightweight, and intuitive data consolidation tools. If you are struggling with complex Excel functions or costly subscriptions, try WPS Office for a seamless spreadsheet experience.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install and Launch: Run the installation file and launch the WPS Spreadsheets application.
- 3. Open Your Excel File: Open your existing .xlsx workbooks directly in WPS Spreadsheets with zero formatting or data loss.
Frequently Asked Questions
Why do my column headers repeat throughout the combined Power Query data?
This happens when the source worksheets are not formatted as proper Excel Tables. Power Query imports the entire sheet range, treating the top row (the headers) of each subsequent sheet as regular data. To fix this, open your source worksheets and format your data ranges as Tables (Ctrl+T) before running the query.
Can I combine specific worksheets and ignore others in Power Query?
Yes. Before you expand the 'Data' column, click the filter drop-down on the 'Name' or 'Item' column in the Power Query Editor to uncheck any worksheets you want to exclude from the consolidation.
Is it better to use a custom function or expand the Data column?
For most users, expanding the Data column is simpler, faster, and less prone to errors. Custom functions are better suited for advanced scenarios where different transformations must be dynamically applied based on parameters before the data is unified.
Is there a limit to how many worksheets Power Query can combine?
Power Query does not have a strict limit on the number of worksheets; it can easily combine data from over 100 sheets. However, query performance depends heavily on your system's available RAM and the total volume of data being processed.
