Power Query Problems

How to Keep File Names When Expanding Binary Data in Power Query

Algirdas Jasaitis Sep 29, 2026 870 views

Question details

The user needs to retain file names, which contain important date metadata, when importing and expanding multiple binary files from a folder in Power Query.

How to Combine File Names and Binary Data in Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing a folder of files where critical data (like dates) is stored in the file names rather than the file contents.
Observed behavior: When the binary file content is automatically opened and expanded, the original file name column is lost or disconnected from the extracted rows.

Before you start

Ensure that all files in your target folder share the same format (e.g., all Excel workbooks or all CSV files) before attempting to combine them, as different formats require different extraction formulas.

Solution 1Recommended

Use a Custom Column to Extract Data Before Expanding

By adding a custom column that manually converts the binary content into a table, you can keep the file name column alongside your data before expanding it.

The default 'Combine Files' button in Power Query sometimes removes standard metadata columns. By transforming the data manually with a custom column, you force Power Query to associate the file name (or date) with every row of the extracted binary file.

Import from Folder

In Excel, go to the Data tab, select 'Get Data', choose 'From File', and then 'From Folder'. Select your target folder and click 'Transform Data' instead of 'Combine'.

Isolate the Necessary Columns

In the Power Query Editor, locate the 'Name' column (which contains your dates) and the 'Content' column (which contains the binary data). Hold CTRL to select both, right-click one of the headers, and choose 'Remove Other Columns'.

Add a Custom Column

Go to the 'Add Column' tab and click 'Custom Column'. Name the new column 'Data'. If you are importing Excel files, enter the formula: Table.AddColumn(RenamedDateCol, "Data", each Excel.Workbook([Content], true)). If importing CSV files, use: Csv.Document([Content]).

Expand the Custom Column

Click the small Expand icon (two diverging arrows) at the top right of your new 'Data' column. Select the columns you want to extract and click 'OK'. Power Query will now repeat the original file name (date) for every imported row.

Use a Custom Column to Extract Data Before Expanding

Formula Case Sensitivity: Remember that M code in Power Query is case-sensitive. Ensure you type 'Excel.Workbook' and 'Csv.Document' exactly as shown with proper capitalization.

Free Microsoft Office alternative

Manage and Analyze Data Seamlessly with WPS Office

While advanced M-code scripting is a specific feature of Microsoft Excel's Power Query, WPS Spreadsheets provides a highly compatible, free, and lightweight alternative for your everyday data consolidation, formatting, and pivot table analysis needs.

1. Download and Install WPS Office: Get the free installation package from the official WPS website and follow the setup instructions.
2. Open Your Data Files: Launch WPS Spreadsheets and open your existing Excel workbooks or CSV files with complete formatting retention.
3. Analyze Your Data: Use the Data tab to access features like Consolidation, Pivot Tables, and advanced filtering to manage your records effortlessly.

Free to use with a highly intuitive and familiar user interfaceExcellent compatibility with Microsoft Excel formats (.xls, .xlsx, .csv)Built-in advanced data consolidation and pivot table functionalitiesLightweight architecture ensures smooth performance on all devices

microsoft office alternative - wps office

Frequently Asked Questions

Can I format the file name column to only show the date before expanding?

Yes. Before adding the custom column, you can use the 'Transform' tab features like 'Extract' or 'Replace Values' on the Name column to remove file extensions (e.g., removing '.xlsx') and leave only the date text.

Why do I get an 'Expression.Error' when using Excel.Workbook?

This error typically occurs if there is a non-Excel file (like a hidden system file, PDF, or CSV) in the folder. Ensure you filter the folder contents by the 'Extension' column to only include '.xlsx' or '.xls' files before adding the custom column.

Will this setup automatically update when I add new files to the folder?

Yes. Once this query is loaded into your workbook, you can simply add new files to the source folder and click 'Refresh All' in the Data tab. Power Query will automatically extract the new file names and append the new data.

How do I extract data from CSV files instead of Excel workbooks?

When creating the Custom Column, replace the Excel formula with Csv.Document([Content]). You may also need to promote headers after expanding the data depending on your CSV structure.

Excel & Data Problems