The Frustrating Reality of Static Queries

Imagine you’re consolidating sales reports from dozens of Excel files. At first, things go smoothly. You connect your folder in Power Query, combine your data, and build your report. But then—boom!—a new column gets added to next month’s file, and your dashboard breaks. Why? Because Power Query didn’t pick up the new column automatically. Static column selection strikes again.
In today’s world of ever-evolving datasets, that simply doesn’t cut it.
That’s exactly why dynamic column expansion in Power Query is so critical. In this step-by-step walkthrough, I’ll show you how to solve this once and for all.
By the end of this article, you’ll know how to Dynamically Expand Columns in Power Query and :

• Connect to a folder with multiple Excel files
• Combine data across all sheets
• Handle headers and formatting inconsistencies
• Automatically adapt to new or changing columns
• (Bonus) Include the sheet name each row comes from

Let’s dive into the magic of Power Query, and turn a rigid workflow into a flexible data machine.

Step 1: Connect Power Query to a Folder of Excel Files

Let’s start with the basics.

Open Excel > Data > Get Data > From File > From Folder.

Browse to the folder where your Excel files are stored. Click OK.

Power Query will preview a table showing all files in the folder. Click Transform Data.

Inside the query editor, you’ll now see columns like Content, Name, Extension, Date accessed, and more.

What you care about is Content — that’s the binary file. Keep this column and remove the rest.

Step 2: Add a Custom Column to Extract All Sheet Tables

Here’s the trick: Each Excel file might have multiple sheets. To extract them all:

Go to Add Column > Custom Column

Excel.Workbook([Content], true)

This returns a nested table for each file. Click the small icon next to the new column to expand it. You’ll see metadata columns like “Name, Data, Item, Kind, and Hidden”.

Filter “Kind” to Sheet or Table, depending on how your files are structured.

Now, expand the Data column — but stop right there.

Step 3: The Problem — Static Column Selection

When you expand “Data”, Power Query shows all columns currently in the first file. You manually check the boxes for which columns to load.

But what happens when a new column appears in future files?

Nothing. Power Query won’t recognize it. You’ll have to manually re-edit the query to include the new field.

This is the static column problem.

Luckily, there’s a solution.

Step 4: Enable Dynamic Column Expansion in M Code

Here’s the simple yet powerful fix.

When expanding the “Data” column, instead of clicking column names manually, edit the M code like this:

= Table.ExpandTableColumn(#"Previous Step", "Data", Table.ColumnNames(#"Previous Step"{0}[Data]))

What’s happening?

• “Table.ColumnNames” – grabs all column names from the first row’s inner table.
• These are dynamically passed to the expand step.
• Result: Even if new columns are added in future Excel files, Power Query will pick them up automatically.

It’s elegant. It’s scalable. It works.

Step 5: Promote Headers Dynamically (Without Breaking)

Another gotcha: When files are combined, the header row is sometimes treated as data.

To handle this cleanly:

After expanding the Data table, use:

Table.PromoteHeaders([Data], [PromoteAllScalars=true])

Apply it inside a “Table.TransformColumns” step if needed across all nested tables.

This ensures the first row in each sheet becomes the actual column header — crucial when dealing with raw exported files.

Step 6: Add Sheet Name as a Column (Optional but Useful)

Let’s say each file has sheets named “Sales_Jan”, “Sales_Feb”, etc. Wouldn’t it be nice to trace each row back to its origin?

Easy.

Before expanding “Data”, keep the “Name” column from the “Excel.Workbook” output. Rename it to “SheetName”.

After expanding and cleaning your data, you’ll now see the original sheet name next to each row — useful for debugging or analysis.

Step 7: Final Touches — Combine, Clean, and Load

Your query should now follow this rough logic:

• Connect to folder
• Get binary content of each file
• Use “Excel.Workbook” to extract sheet tables
• Filter sheet type
• Expand “Data” using dynamic column names
• Promote headers
• Add sheet names (optional)
• Clean data types
• Remove unwanted rows (like empty or error-filled)

Once done, click Close & Load to drop your dynamically refreshed dataset into Excel.

Boom. No more broken dashboards when a new column appears next month.

Full Sample M Code (for Reference)

Here’s a condensed version of the full logic:

let
Source = Folder.Files("C:\Your\Folder\Path"),
GetContent = Table.SelectColumns(Source, {"Content"}),
AddSheets = Table.AddColumn(GetContent, "Sheets", each Excel.Workbook([Content], true)),
ExpandedSheets = Table.ExpandTableColumn(AddSheets, "Sheets", {"Name", "Data", "Kind"}),
FilterSheets = Table.SelectRows(ExpandedSheets, each [Kind] = "Sheet"),
AddSheetName = Table.AddColumn(FilterSheets, "SheetName", each [Name]),
ExpandedData = Table.TransformColumns(AddSheetName, {"Data", each Table.PromoteHeaders(_, [PromoteAllScalars=true])}),
ExpandedAll = Table.ExpandTableColumn(ExpandedData, "Data", Table.ColumnNames(ExpandedData{0}[Data]))
in
ExpandedAll

Tweak as needed, especially for custom data types or complex validations.

Conclusion: Why Dynamic Queries Future-Proof Your Workflow

Static queries break. Dynamic queries scale.

By learning how to dynamically expand columns in Power Query, you’re not just solving a technical problem — you’re future-proofing your data workflow. This technique saves time, reduces maintenance, and empowers you to build robust data models across evolving datasets.

As Peter Drucker once said:

“What gets measured gets managed.”

With Power Query on your side — and now dynamically expanding — there’s nothing stopping you from mastering your data.

Sample Data.

Sample Data file

Related Article

AI in 2025 – Advanced Business Use Cases with Excel and Power BI.
Advanced M Code Techniques for Complex Data Transformations
Power Query Dynamically add Extra Columns – Microsoft Fabric Community