Data Import & Export

How to Fix Excel CSV Import Loading Only Two Columns in Power Query

Partner Editor Oct 1, 2026 868 views

Question details

Users experience an issue where Excel Power Query imports only two columns from a semicolon-delimited CSV file instead of all the available data.

Fix Excel CSV Import Loading Only Two Columns

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing semicolon-delimited CSV files or combining multiple CSV files from a folder using Power Query.
Observed behavior: Power Query infers the structure from the first few rows and generates a Csv.Document step specifying Columns=2, causing it to ignore the remaining data columns.

Before you start

Verify the actual number of columns in your dataset by opening the CSV file in a plain text editor like Notepad before modifying your query.

Solution 1Recommended

Adjust the Column Count in Power Query Editor

Directly modify the M code in the generated Csv.Document step to specify the correct number of columns.

Power Query often infers the column count based on the first few rows of the file. If those early rows only contain two fields, it sets a hard limit. You can manually override this limit in the Power Query Editor.

Launch Power Query Editor

In Excel, navigate to the 'Data' tab and click on 'Get Data', then select 'Launch Power Query Editor' to view your queries.

Locate the Source Step

In the 'Applied Steps' pane on the right side of the editor, click on the 'Source' step for your CSV import.

Edit the Formula

Look at the formula bar at the top for the Csv.Document function. Locate the parameter that says 'Columns=2' and change the number to match the actual maximum number of columns in your file (for example, change it to 'Columns=6').

Update Subsequent Steps

Review the steps that follow the 'Source' step, such as 'Changed Type'. If the type conversion step still references only two columns, delete the step and let Power Query automatically recreate it by clicking 'Detect Data Type' on the Transform tab.

Close and Load

Click 'Close & Load' on the Home tab to apply your changes and load the complete dataset into your Excel worksheet.

Adjust the Column Count in Power Query Editor

Folder-Based Combinations: If you are combining files from a folder, remember to apply this exact change to the 'Transform Sample File' helper query as well, so all files are processed with the correct column count.

Solution 2

Add Missing Delimiters to Early Rows

Modify the raw CSV file to ensure Power Query accurately detects the correct number of columns right from the start.

Free Microsoft Office alternative

Open CSV Files Effortlessly with WPS Office

Tired of dealing with complicated Power Query steps and M code just to open a simple CSV file? WPS Office provides a seamless, lightweight, and free alternative to Microsoft Office. It automatically detects CSV delimiters correctly, saving you time and frustration.

Easily open and import CSV files without writing complex query formulas.Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Lightweight installation ensures faster startup and smooth data processing.Familiar, user-friendly interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query only load two columns from my CSV?

Power Query infers the structure of an imported file by analyzing the first few rows. If the first 10 rows only contain data for two fields, it assumes the entire document only has two columns and hardcodes 'Columns=2' in the import steps.

How do I refresh the data after changing the column count in Power Query?

Once you edit the Columns parameter in the formula bar of the Power Query Editor, click the 'Close & Load' button. To update the data later, simply click 'Refresh All' on the Data tab in the main Excel ribbon.

Does this column issue affect combining files from a folder?

Yes. When you use the 'Combine Files' feature, Excel creates a helper transformation query. If the sample file evaluated by Power Query has only two columns in its early rows, the entire combination will be limited to two columns. You must edit the 'Transform Sample File' query to fix it for all files.

Excel & Data Problems