Power Query Problems

How to Find a Specific Header in Multiple CSV Files Using Excel Power Query

Camila Milosovich Oct 10, 2026 869 views

Question details

The user needs a method to inspect multiple CSV files in a folder to determine if each individual file contains a specific column header.

How to Find a Specific Header in Every CSV File with Excel Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to locate a specific data header across numerous CSV files that have varying header structures.
Observed behavior: A standard append query in Power Query combines the data but does not clearly indicate whether each individual underlying file contains the specified header.

Before you start

Ensure all the CSV files you want to inspect are saved in a single folder on your computer and take note of the exact header name you are looking for.

Solution 1Recommended

Use a Custom Column in Power Query

Extract the first line of each CSV file directly from the folder view using a custom Power Query formula.

Instead of appending all files, you can read the binary content of each file from the folder level, isolating just the first row (the headers) to search for your required text.

Load Folder into Power Query

Open Excel, go to the Data tab, click 'Get Data', select 'From File', and choose 'From Folder'. Select the directory containing your CSV files.

Transform Data

In the preview window that appears, click 'Transform Data' (do not click Combine). This opens the Power Query Editor showing the list of files.

Add a Custom Column

Go to the Add Column tab and click 'Custom Column'. In the formula box, enter the following exact code: =Text.Split(Text.BeforeDelimiter(Text.FromBinary([Content]),"#(cr)"),",")

Expand the Column

Click the small expand icon on the header of your newly created custom column and select 'Expand to New Rows'. This lists out all the headers found in each file.

Filter for Your Header

Click the filter drop-down on the expanded column, uncheck '(Select All)', and check only the specific header (e.g., 'ABC') you are looking for. The remaining rows will show exactly which files contain that header.

Formula Explanation: The custom formula works by taking the raw binary [Content] of the file, reading text up to the first carriage return "#(cr)" (which represents the first line), and splitting that text by commas.

Solution 2

Run a VBA Macro to Check CSV Headers

Use a VBA script to automate the process of checking each file's first line and generating a summary worksheet.

Free Microsoft Office alternative

Manage and Analyze CSV Data Seamlessly with WPS Office

If you handle extensive data sets and need an efficient, cost-effective tool, consider WPS Office. It provides a robust, lightweight, and highly compatible alternative for processing spreadsheets without a steep learning curve.

1. Download the Software: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Import CSV Files: Launch WPS Spreadsheet, navigate to the Data tab, and use the import text features to load your CSV datasets.
3. Analyze Your Data: Easily apply filters and text-to-columns functions to organize your headers and extract the insights you need.

Completely free to use with extremely low system requirements.High format compatibility with Microsoft Excel files, including .xlsx and .csv.Familiar interface design ensuring a seamless transition for new users.Powerful built-in data filtering, sorting, and analytical tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I search for multiple headers at once using Power Query?

Yes. Once you have expanded the custom column to new rows, you can use the filter drop-down to select multiple target headers, or use the 'Text Filters' option to search for various conditions simultaneously.

Why does the Power Query formula use "#(cr)"?

The string "#(cr)" represents a carriage return in Power Query's M language. It is used in the formula to isolate the first row (the headers) from the rest of the CSV file's data by grabbing everything before the first line break.

Do I need to load and combine all the CSV data to check the headers?

No. By using the 'Transform Data' option and extracting data directly from the binary [Content] column, you are only querying the first row of each file, avoiding the performance hit of appending all the full datasets.

Will the VBA macro method modify my original CSV files?

No, the VBA macro simply reads the files using an Input stream. It evaluates the first line of text and then closes the file safely without saving or making any modifications.

Excel & Data Problems