Power Query Problems

How to Combine and Transform Multiple Excel Sheets Using Power Query

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to combine multiple workbooks from a folder, transform each worksheet using a custom function to unpivot data and adjust headers, and append them into a single database-style table while keeping the source worksheet name.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating and cleaning complex data structures across multiple worksheets and files stored in a single directory.
Observed behavior: Data needs to be transformed, unpivoted, and tagged with the source worksheet name before appending to ensure a clean, database-ready output.

Before you start

Ensure all your source Excel files are stored in a single, dedicated folder. It is highly recommended to test your query on a copy of the files before applying it to your production data.

Solution 1Recommended

Use a Custom Power Query Function to Transform and Combine Files

Connect Power Query to your folder and apply a custom M function to transform each sheet individually before expanding and appending the consolidated data.

When dealing with complex data structures across multiple sheets, expanding the tables immediately can cause messy or misaligned columns. Creating a custom function ensures that every individual sheet is cleaned, formatted, and unpivoted before they are merged together.

Connect to the folder

Open Excel, navigate to the Data tab, and select 'Get Data' > 'From File' > 'From Folder'. Browse to the folder containing your Excel files and click Open.

Transform sample data

Click 'Transform Data' to open the Power Query Editor. Filter the file list if necessary to exclude hidden files or unwanted formats. Keep only the 'Content' and 'Name' columns.

Create the custom function

Create a new blank query to define your custom function. Configure it to accept a worksheet as a parameter, promote headers, remove unnecessary rows or columns, and unpivot the data. Add a step to include the worksheet name.

Invoke the function

In your main folder query, add a custom column that invokes your newly created function for each sheet's data.

Expand and load

Expand the column containing the transformed tables. Review your appended data to ensure consistency, then click 'Close & Load' to output the final database-style table into your workbook.

Data Privacy Prompts: If you are prompted with data privacy warnings when combining files, ensure your privacy levels are set consistently across the current workbook and the source folder.

Free Microsoft Office alternative

Looking for a Fast and Compatible Excel Alternative?

While advanced Power Query M-code operations are specific to Microsoft Excel, WPS Office provides a lightweight, highly compatible alternative. If you just need standard data consolidation, WPS Spreadsheet offers built-in intuitive tools to merge multiple sheets without writing code.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open your files: Open your existing .xlsx workbooks securely with WPS Spreadsheet.
3. Consolidate Data: Navigate to the Data tab and use the Consolidate tool to merge ranges from different sheets visually.

Seamlessly compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in Data Consolidate feature for merging multiple sheets quickly.Lightweight software that runs smoothly on almost any device.Familiar user interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine specific sheets from multiple files instead of all of them?

Yes. Before expanding the tables in the Power Query Editor, you can apply text filters to the 'Item' or 'Name' column to include only the specific worksheet names you want to consolidate.

Why do my column headers not match when appending multiple sheets?

Power Query is case-sensitive and requires exact header matches. Ensure your custom function standardizes column names (e.g., trimming spaces or converting all headers to proper case) before the expansion step.

How do I retain the original file or sheet name in the final combined table?

Do not delete the 'Source.Name' column (for the file name) and the 'Item' column (for the sheet name) before expanding the tables. Keeping them ensures they will be included alongside your appended data rows.

Excel & Data Problems