Excel Off The Grid will show you how to work smarter, not harder with Microsoft Excel.
Excel Power Query can combine files from a folder in one query, avoiding the separate helper queries created by the standard Combine Files command. This approach is useful for Excel users who want to inspect or maintain the file-import and transformation steps together. Excel Off The Grid demonstrates the technique with both a straightforward set of stock files and budget files that need reshaping before they can be appended.
The standard folder-combine process uses a sample file to define transformations and creates supporting queries, including a transformation function. Power Query applies that logic across the files and combines the results. The single-query alternative performs the workbook-reading and table-combining steps directly in the folder query instead.
For Excel workbooks, the key function is Excel.Workbook. It reads workbook content from a binary value; in the folder listing, that binary content is available in the Content column. The result includes workbook objects such as sheets and their associated data tables.
Table.Combine appends a list of tables. That matters because expanding the nested tables directly can hardcode the visible column names, such as Column1 through Column9. The demonstration uses Table.Combine on the list of Data tables instead, so it does not rely on that same fixed expansion step.
This method is aimed at Excel users working in Power Query who want to combine multiple workbooks from a folder and keep the steps in one query. The examples start with the folder connector and files available in a folder. For the basic example, the stock workbooks have a consistent, tidy structure with a single header row and columns in the same order.
The more complex budget example has a different shape: months appear across columns, and each file represents a different year. Simply stacking those tables would put values from different months under the same column headings. The query must first promote the headers in each workbook and reshape its data by unpivoting the month columns.
For more Excel guidance, the practical distinction is whether the files already share a usable table structure. Matching layouts need fewer per-file transformations; cross-tab or differently organized data needs additional steps before combining.
For the simple example, create a folder query and transform the file listing. Rather than selecting the Combine Files icon, add a custom column to read each workbook. The resulting workbook objects expose the sheet data, which can then be combined into one table.
The budget example applies the transformations before appending. It uses Table.TransformColumns to apply a transformation to each nested Data table. The underscore represents the current table being transformed, so the header and unpivot operations run separately for every workbook.
In the example, the unpivoted columns initially appear as Attribute and Value; the demonstration renames Attribute to Date. The result is a single clean table with the budget files reshaped consistently before they are combined.
A single-query design removes the generated helper-query objects, but it does not remove the need to define transformations that fit the files. The simple example works because the columns are already in the same order and the data has no junk rows above the header. Repeated header rows still need handling after combining.
For cross-tab data, promoting headers and unpivoting must happen for each file, not just once after combining. Otherwise, different years' month columns can be misaligned. The column names used by Table.UnpivotOtherColumns also need to match the identifier columns present in the nested tables; the demonstration corrects the references before the step works.
The standard Combine Files command remains an alternative: it generates helper queries and a function that applies the sample-file transformations to the other files. Use that route when the generated structure suits the task; choose the single-query pattern when you prefer to keep the transformation logic in the main query. The examples here focus on Excel workbooks and should be treated as patterns to adapt to the structure of the files being combined.
Create a folder query, add a custom column using Excel.Workbook([Content]), retain the nested Data tables, and combine them with Table.Combine.
Expanding the column can hardcode the visible column names. Table.Combine appends the list of nested tables without relying on that fixed expansion.
Promote headers and unpivot the month columns in each nested Data table using Table.TransformColumns before combining the tables.