Excel Power Query: combine files without helper queries
Excel

Excel Power Query: combine files without helper queries

by HubSite 365 about Excel Off The Grid

Excel Off The Grid will show you how to work smarter, not harder with Microsoft Excel.

Use Excel.Workbook, Table.TransformColumns and Table.Combine to combine folder files in one Power Query query.

Key insights

  • Start with a folder query and add a custom column that uses Excel.Workbook on each file's Content binary.
  • Use Table.Combine on the nested Data tables to append files without hardcoding a fixed set of columns.
  • Promote headers within each nested table when files repeat their header row.
  • For cross-tab budgets, promote headers and unpivot each file before combining the results.

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.

What it does

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.

Who it is for and what you need

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.

How it works

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.

  1. In Power Query, choose a new query from a folder, select the folder, and choose Transform Data.
  2. Use Add Column > Custom Column. Name the column, for example, xl, and enter Excel.Workbook([Content]).
  3. Remove other columns from the workbook-object results, expand the workbook contents, and remove other columns again to retain the Data tables.
  4. Create a new step and use Table.Combine with the Data column as the list of tables. This appends the files without expanding a fixed list of column names.
  5. If each file includes a header row, promote the first row to headers and remove the repeated header rows from the combined result.

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.

  1. After creating the folder query and extracting the Data tables, create a step with Table.TransformColumns for the Data column.
  2. Within that transformation, use Table.PromoteHeaders on each nested table, passing the current table as the input.
  3. Use another Table.TransformColumns step to apply Table.UnpivotOtherColumns to each Data table. Keep the identifier columns, such as Account Code, Account, and Cost Centre, and unpivot the remaining month columns.
  4. Check the column names referenced by the unpivot step against the actual tables, then combine the transformed Data tables and expand the result.

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.

Limitations and alternatives

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.

Sources

Frequently asked questions

How do I combine Excel files in Power Query without helper queries?

Create a folder query, add a custom column using Excel.Workbook([Content]), retain the nested Data tables, and combine them with Table.Combine.

Why use Table.Combine instead of expanding the Data column?

Expanding the column can hardcode the visible column names. Table.Combine appends the list of nested tables without relying on that fixed expansion.

How do I combine budget files with months across columns?

Promote headers and unpivot the month columns in each nested Data table using Table.TransformColumns before combining the tables.