Power Query: Merge Excel Files with Dynamic Headers | DAXified

📊 Power Query Mastery

Merge Multiple Excel Files with Dynamic Header Selection

Build a Flexible, User-Controlled File Consolidation System — No VBA Required!

🎯 What You’ll Build

Imagine having 50+ Excel files in a folder — all with similar structures but you only need specific columns. Manually copying and pasting? That’s hours of tedious work and a recipe for errors.

In this guide, you’ll create a dynamic Power Query solution that:

✓

Automatically scans all Excel files in a folder

✓

Lets YOU choose which columns to import via a simple checkbox table

✓

Merges everything into one clean, consolidated table

✓

Refreshes with one click when new files arrive

💡 Perfect For: Internal Auditors consolidating audit reports, Finance teams merging monthly statements, HR combining employee data from multiple branches, or anyone dealing with repetitive file consolidation tasks.

🔄 How It Works — The Big Picture

📁 Folder Path
tblSourcePath

→

📋 Header Filter
TblHeaders

→

          ⚡ Power Query

          Merge Engine

→

📊 Merged Output
Clean Data

The magic happens through two Excel tables that control your query:

Table Name	Purpose	What You Define
`tblSourcePath`	Tells Power Query WHERE to look	Folder path containing your Excel files
`TblHeaders`	Tells Power Query WHAT columns to extract	Column names + TRUE/FALSE checkbox

🛠️ Step 1: Create the Control Tables

Table 1: Source Path (tblSourcePath)

Create a single-column table with your folder path:

FolderPath
C:\Reports\MonthlyData\

Important

Include the trailing backslash \ at the end of the folder path. Select the cell, go to Insert → Table, and name it tblSourcePath in the Table Design tab.

Table 2: Header Selection (TblHeaders)

Create a two-column table listing ALL possible headers from your source files:

Header	Include
Invoice Number	TRUE
Customer Name	TRUE
Amount	TRUE
Internal Notes	FALSE
Created By	FALSE

💡 Pro Tip: Use Data Validation to create a dropdown for the Include column. This gives you a clean TRUE/FALSE selector instead of manual typing.

📝 Step 2: The Header Filter Query

This query reads your TblHeaders table and extracts ONLY the column names marked as TRUE. It returns a list that the main query will use.

let // Step 1: Read the TblHeaders table from Excel Source = Excel.CurrentWorkbook(){[Name=“TblHeaders”]}[Content], // Step 2: Ensure correct data types #“Changed Type” = Table.TransformColumnTypes( Source, {{“Header”, type text}, {“Include”, type logical}} ), // Step 3: Keep only rows where Include = TRUE #“Filtered Rows” = Table.SelectRows( #“Changed Type”, each ([Include] = true) ), // Step 4: Keep only the Header column #“Removed Other Columns” = Table.SelectColumns( #“Filtered Rows”, {“Header”} ), // Step 5: Convert to a List (required for dynamic expansion) Header = #“Removed Other Columns”[Header] in Header

🔍 What Each Step Does

Excel.CurrentWorkbook()

Connects to a named table in the current workbook. The {[Name="TblHeaders"]} syntax finds the specific table.

Table.TransformColumnTypes()

Ensures “Header” is text and “Include” is a logical (TRUE/FALSE) value. This prevents type mismatch errors.

Table.SelectRows()

Filters the table to keep only rows where Include = true. This is your checkbox logic!

Table.SelectColumns()

Removes the “Include” column — we only need the header names now.

[Header] — List Extraction

Converts the single-column table to a List. This is crucial — the main query needs a list, not a table.

⚡ Step 3: The Main File Merge Query

This is the powerhouse query that scans your folder, opens each Excel file, and extracts only the selected columns.

let // ═══════════════════════════════════════════════════ // SECTION 1: GET FOLDER PATH FROM EXCEL TABLE // ═══════════════════════════════════════════════════ SourceFolder = Excel.CurrentWorkbook(){[Name=“tblSourcePath”]}[Content], FolderPath = SourceFolder[FolderPath]{0}, // ═══════════════════════════════════════════════════ // SECTION 2: SCAN ALL FILES IN THE FOLDER // ═══════════════════════════════════════════════════ Source = Folder.Files(FolderPath), // Keep only essential columns #“Removed Other Columns” = Table.SelectColumns( Source, {“Content”, “Name”} ), // Rename for clarity #“Renamed Columns” = Table.RenameColumns( #“Removed Other Columns”, {{“Name”, “FileName”}} ), // ═══════════════════════════════════════════════════ // SECTION 3: OPEN EACH EXCEL FILE // ═══════════════════════════════════════════════════ ToWorkbook = Table.TransformColumns( #“Renamed Columns”, {{“Content”, each Excel.Workbook(_, true), type table}} ), // Expand workbook contents #“Expanded Content” = Table.ExpandTableColumn( ToWorkbook, “Content”, {“Name”, “Data”, “Item”, “Kind”, “Hidden”} ), // ═══════════════════════════════════════════════════ // SECTION 4: FILTER TO SHEETS ONLY (No Tables/Ranges) // ═══════════════════════════════════════════════════ #“Filtered Rows” = Table.SelectRows( #“Expanded Content”, each [Kind] = “Sheet” ), // Exclude temporary Excel files (start with ~) #“Filtered Rows1” = Table.SelectRows( #“Filtered Rows”, each not Text.StartsWith([FileName], “~”) ), // Clean up metadata columns #“Removed Columns” = Table.RemoveColumns( #“Filtered Rows1”, {“Name”, “Item”, “Kind”, “Hidden”} ), // ═══════════════════════════════════════════════════ // SECTION 5: EXPAND DATA WITH SELECTED HEADERS ONLY // ═══════════════════════════════════════════════════ #“Expanded Data” = Table.ExpandTableColumn( #“Removed Columns”, “Data”, TblHeaders, // ← Dynamic list from our filter query! TblHeaders ) in #“Expanded Data”

🔬 Deep Dive: Key Concepts Explained

📁 Folder.Files() — The Folder Scanner

This function returns a table with ALL files in the specified folder. Each row contains binary content (Content) and metadata like filename, extension, and path.

📖 Excel.Workbook() — The File Opener

Converts binary content into a readable Excel workbook structure. The true parameter tells Power Query to use the first row as headers.

Excel.Workbook(binaryContent, true) ↑ Use first row as headers

🔄 Table.ExpandTableColumn() — The Magic Expander

This is where the dynamic header selection happens. Instead of hardcoding column names, we pass the TblHeaders list:

Table.ExpandTableColumn( sourceTable, “Data”, // Column containing nested tables TblHeaders, // Columns to extract (from our list!) TblHeaders // Names in the output )

🎯 Why This Works: TblHeaders isn’t a static list — it’s a reference to another query. Every time you refresh, Power Query re-evaluates TblHeaders, reads your current TRUE/FALSE selections, and expands only those columns. Pure elegance!

🚫 Filtering Temporary Files

Excel creates temporary files (starting with ~) when workbooks are open. This line prevents errors:

each not Text.StartsWith([FileName], “~”)

📋 Copy-Paste Ready Queries

Here are both queries ready to copy. Follow the naming guide below carefully — the names must match exactly for the queries to connect!

Query 1: TblHeaders

Query Name: TblHeaders (must be exactly this name)

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="TblHeaders"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}, {"Include", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Include] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Header"}),
    Header = #"Removed Other Columns"[Header]
in
    Header

Query 2: MergedData (Main Query)

Query Name: MergedData (or any name you prefer)

M Code

let
    SourceFolder = Excel.CurrentWorkbook(){[Name="tblSourcePath"]}[Content],
    Custom1 = SourceFolder[FolderPath]{0},
    Source = Folder.Files(Custom1),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Name", "FileName"}}),
    ToWorkbook = Table.TransformColumns(
        #"Renamed Columns",
        {{"Content", each Excel.Workbook(_, true), type table}}
    ),
    #"Expanded Content" = Table.ExpandTableColumn(ToWorkbook, "Content", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Content", each [Kind] = "Sheet"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([FileName], "~")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name", "Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", TblHeaders, TblHeaders)
in
    #"Expanded Data"

🔗 Critical: How Names Connect Everything

The magic of this solution depends on exact name matching between your Excel tables and Power Query references. Here’s the complete map:

Excel Table Name	Referenced In Query	What It Controls
`tblSourcePath`	`Excel.CurrentWorkbook(){[Name="tblSourcePath"]}`	Folder location of your Excel files
`TblHeaders`	`Excel.CurrentWorkbook(){[Name="TblHeaders"]}`	List of column headers with TRUE/FALSE
↓ Query-to-Query Connection ↓
`TblHeaders` (Query)	`Table.ExpandTableColumn(..., TblHeaders, TblHeaders)`	Passes the filtered header list to main query

⚠️ Common Mistake: If you name your Excel table tblheaders (lowercase) but the query says "TblHeaders", it will fail! Power Query table names are case-sensitive.

🚀 Complete Implementation Guide

Phase 1: Create Excel Tables (5 minutes)

Create the Source Path Table

In a new worksheet, type your folder path in cell A1 with header “FolderPath” above it:

A
FolderPath
C:\MyReports\SalesData\

Convert to Table & Name It

Select cells A1:A2 → Press Ctrl + T → Check “My table has headers” → Click OK

Name the Table Exactly

With table selected, go to Table Design tab → In the Table Name box (top-left), type: tblSourcePath

Create the Headers Table

In another area or worksheet, create this structure:

Header	Include
Invoice No	TRUE
Customer	TRUE
Amount	TRUE
Notes	FALSE
Created By	FALSE

Convert & Name as TblHeaders

Select all data including headers → Ctrl + T → Name the table: TblHeaders

💡 Pro Tip: For the “Include” column, use Data Validation! Select the Include cells → Data → Data Validation → Allow: List → Source: TRUE,FALSE. Now you get a clean dropdown!

Phase 2: Create Power Queries (5 minutes)

Open Power Query Editor

Go to Data tab → Get Data → Launch Power Query Editor

Create First Query (TblHeaders)

Home → New Source → Other Sources → Blank Query

Open Advanced Editor

Click Advanced Editor in the Home tab → Delete all existing code → Paste Query 1: TblHeaders code from above

Name the Query (CRITICAL!)

In the right panel under Query Settings → Name, type exactly: TblHeaders

Create Second Query (MergedData)

Repeat: New Source → Blank Query → Advanced Editor → Paste Query 2: MergedData code → Name it MergedData

Load to Excel

Select MergedData query → Home → Close & Load → Your merged data appears in a new worksheet!

🔄 To Refresh: After adding new files to your folder or changing TRUE/FALSE selections, just right-click the output table → Refresh. Everything updates automatically!

✅ Pre-Flight Checklist

Before clicking “Close & Load”, verify these items:

✓	Item	How to Verify
☐	Excel table named `tblSourcePath`	Click table → Check Table Design tab → Table Name box
☐	Excel table named `TblHeaders`	Click table → Check Table Design tab → Table Name box
☐	Folder path ends with backslash `\`	Check tblSourcePath → Should be `C:\Folder\` not `C:\Folder`
☐	At least one header marked TRUE	Check TblHeaders → Include column has at least one TRUE
☐	Query named exactly `TblHeaders`	Power Query Editor → Right panel → Query Settings → Name
☐	Header names match source files exactly	Open one source file → Compare column names with TblHeaders table
☐	Source files are closed	Close all Excel files in the source folder before refreshing

💼 Real-World Use Cases

Industry	Scenario	Benefit
Internal Audit	Merge 100+ branch audit reports	Extract only Observation, Risk Rating, Status — skip internal notes
Finance	Consolidate monthly P&L from subsidiaries	Select Revenue, Expenses, Net Profit — exclude draft columns
HR	Combine employee data from regional offices	Import Name, Department, Salary — skip sensitive personal info
Sales	Merge weekly sales reports	Get Product, Quantity, Revenue — exclude rep commission details

💡 Pro Tips & Best Practices

⚡ Performance Tip: Keep your source files closed when refreshing. Open files create locks and temporary files that can slow down or break the query.

🔧 Troubleshooting: If you get “Column not found” errors, ensure the header names in TblHeaders exactly match the column headers in your source files — including spaces and capitalization!

📂 Folder Structure: This query processes the root folder only. For subfolders, replace Folder.Files() with Folder.Files(FolderPath, [Recursive=true]).

🔒 Data Privacy: If prompted about Privacy Levels, set both data sources to “Organizational” to allow them to communicate. Go to File → Options → Privacy and select “Ignore Privacy Levels”.

🎓 Master Power Query with DAXified

Join our community for more Excel automation tutorials, Power BI guides, and real-world solutions.

▶️ Watch Video Tutorials 💬 Join WhatsApp Community

🏁 Wrapping Up

You’ve just built a production-ready file consolidation system that would take hours to replicate manually. The beauty of this approach is its flexibility — change your header selections anytime, drop new files in the folder, and hit Refresh. Power Query handles the rest.

This pattern — using Excel tables to control Power Query behavior — is a game-changer for building user-friendly, maintainable solutions. No VBA, no complex formulas, just clean M code doing the heavy lifting.

📧 Questions? Reach out at k.casatish@daxified.co.in — I’d love to hear how you’re using this in your workflows!