Power Query Problems

How to Combine CSV Files with Different Columns Using Excel Power Query

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user needs to combine multiple CSV files that have varying column structures but share a common identifier, without writing a VBA macro.

How to Combine CSV Files with Different Columns Using Excel Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Merging multiple CSV data sources with different columns into a single, unified dataset based on a shared key.
Observed behavior: The user is looking for a macro-free method to automatically align and transform data across multiple files using Power Query.

Before you start

Ensure all the CSV files you want to combine are placed into a single, dedicated folder on your computer. Verify that the files share at least one common identifier column, such as a Ticket Number, which will be used to merge the records accurately.

Solution 1Recommended

Combine and Merge CSV Files Using Power Query

Use Excel's Get Data from Folder feature to import multiple CSVs, then append or merge queries based on your common identifier column.

Power Query can seamlessly handle CSV files with different column counts. When combining files from a folder, missing columns in specific files will simply display as 'null'. To horizontally align data across files using a shared key like a ticket number, you can use the Merge Queries feature.

Import files from the designated folder

Open Excel, navigate to the 'Data' tab on the ribbon, and select 'Get Data' > 'From File' > 'From Folder'. Browse to the folder containing your CSV files and click 'Open'.

Combine and launch Power Query Editor

In the preview window that appears, click the 'Combine' dropdown button at the bottom and select 'Combine & Transform Data'. Excel will process the files and open the Power Query Editor.

Merge relevant tables by common identifier

If you need to align the data horizontally rather than just appending it, select your primary table query, click 'Merge Queries' on the Home tab, and match the 'Ticket Number' (or common ID) column across your tables to link the data.

Expand nested data columns

After merging, click the expand icon (two diverging arrows) at the top of the newly created column to reveal the specific descriptions, materials, or varying columns you wish to include.

Close and load the combined dataset

Once your transformation is complete and the data looks correct, click 'Close & Load' on the Home tab to export the combined data into a new Excel worksheet.

Combine and Merge CSV Files Using Power Query

Testing Your Transformation: It is highly recommended to test this process first using sample CSV files with any confidential information removed to ensure the table expansion maps correctly.

Free Microsoft Office alternative

Try WPS Office for seamless data management

While Power Query is a feature specific to Microsoft Excel, WPS Office provides a highly compatible, lightweight, and free alternative for managing spreadsheets and combining CSV data. You can easily process massive datasets and merge information using built-in standard functions without expensive subscriptions.

1. Download and Install: Download WPS Office for free from the official website and complete the quick installation.
2. Import your CSV files: Open WPS Spreadsheets, go to the 'Data' tab, and use 'Import Data' to securely load your CSV files into the workbook.
3. Combine data efficiently: Utilize robust built-in functions like VLOOKUP, XLOOKUP, or standard data consolidation tools to align your data across different files via ticket numbers.

Free to use with comprehensive spreadsheet and data management capabilitiesFully compatible with Microsoft Excel formats (.xlsx, .csv, .xls)Lightweight application that runs smoothly even on older devicesFamiliar user interface requiring zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Does combining CSV files with different columns cause data loss in Power Query?

No, Power Query handles mismatched columns automatically. When appending files, any columns that do not exist in a specific CSV file will simply be populated with 'null' values for those rows, ensuring no data is lost.

Do I need to know VBA or write macros to combine these CSV files?

Not at all. Power Query provides a visual, click-based interface to combine, clean, and merge data. You can accomplish complex transformations without writing a single line of VBA or macro code.

Can I automatically update the combined spreadsheet if new CSV files are added?

Yes. If you add new CSV files to the designated source folder, you only need to open your Excel workbook, go to the 'Data' tab, and click 'Refresh All'. Power Query will automatically process the new files and update your final table.

Excel & Data Problems