Power Query Problems

Fix an Excel Power Query Folder Column That Returns Null for Every Row

Phi Hung VoPhi Hung Vo Nov 12, 2026 869 views

Question details

A folder-based Excel Power Query returns null for every row in one column after combining daily report files.

Fix an Excel Power Query Folder Column That Returns Null for Every Row
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The user is combining files from a folder and needs the affected column to be retained and populated from each report.
Observed behavior
One column, such as Column 8, becomes null across all combined rows when the selected sample file contains an empty or missing value for that column.
Before you start

Open the folder query in Excel Power Query Editor and identify the query group created by the Combine Files process. Check that the source reports use consistent headers and that the affected column exists in the files you expect to combine.

Solution 1Recommended

Correct the Transform Sample File Query for the Missing Column

Use this when a folder-combine query returns null for an entire column, especially when the problem began after a file with an empty or missing column was used as the sample file.

Excel Power Query uses the Transform Sample File query to create the transformation applied to every file in the folder. If that sample file has an empty or absent Column 8, Power Query can infer an incorrect structure and return null values for that column in the combined result.

1
Open the folder query group

In Excel, select Data > Queries & Connections, then double-click the folder-combine query to open Power Query Editor. In the Queries pane, expand the helper queries created for the folder combine.

2
Open Transform Sample File

Select the query named Transform Sample File, or a similarly named sample transformation query under the Transform File group. Review the Applied Steps pane to find the first step where the affected column disappears or becomes null.

3
Retain the affected column

At the source, navigation, promoted headers, or removed columns step, make sure Column 8 or its actual header is included. If a Remove Columns step excludes it, select the gear icon for that step or edit the column list to keep the column.

4
Set the correct data type

Select the affected column, then choose Transform > Data Type and assign the intended type, such as Text, Whole Number, or Date. If blanks are valid, use a type that supports null values without removing the column.

5
Refresh the combined query

Select the main folder query, choose Home > Close & Load, and then use Data > Refresh All. Confirm that values from files containing the column now appear instead of null.

Correct the Transform Sample File Query for the Missing Column
Why this fixes all files: Once the sample transformation retains and types the column correctly, Power Query applies that corrected transformation to every file in the folder.
Free Microsoft Office alternative

Use WPS Office for Everyday Spreadsheet Work

If Excel Power Query folder transformations continue to block your reporting workflow, WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. It supports seamless migration of common Excel workbooks and lets you continue reviewing, editing, and sharing daily reports without changing their basic spreadsheet format.

Free and lightweight spreadsheet software for daily report editingFamiliar ribbon-based interface for a smooth move from Microsoft ExcelCompatible with Microsoft Excel .xlsx, .xls, and .csv filesReview column values, headers, formulas, and report structure before combining files
microsoft office alternative - wps office

Frequently Asked Questions

Why does an Excel Power Query folder column become null for every row?

The Transform Sample File query may have used a file where the column was blank or missing. That sample transformation is then applied to all files, which can cause the combined column to return null values.

Where can I find the Transform Sample File query in Excel?

Open Data > Queries & Connections and edit the folder query. In Power Query Editor, look in the Queries pane for helper queries named Transform Sample File, Sample File, Transform File, or similar names created by Combine Files.

Can an empty value in one source file remove a column from the combined query?

An empty cell normally should not remove a column, but a missing column, incorrect header promotion, or a sample-file transformation that excludes the field can cause the combined output to show null values for every row.

How do I stop Power Query from dropping a column during folder combine?

Edit Transform Sample File and check the Source, Navigation, Promoted Headers, Choose Columns, and Remove Columns steps. Keep the affected field in the transformation and set its data type after confirming the column is present.

Why are some rows still null after I fix the sample file?

Those specific source files may genuinely have blank values, missing headers, or a different report layout. Open those files and standardize the affected column name and structure to match the other reports.