Power Query Problems

How to Fix Power Query Missing a Column After Excel Source Changes

Natalie Taylor Oct 16, 2026 870 views

Question details

The user needs to prevent Power Query from dropping or missing the first column when importing data from an Excel file where the first column has become empty.

How to Fix Power Query Missing a Column After Excel Source Changes

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing or refreshing an Excel workbook data source in Power Query where the first column's data varies, sometimes containing values and sometimes being completely blank.
Observed behavior: When the first column (e.g., Column A) is entirely empty, Power Query's automatic used-range detection treats Column B as the new first column, effectively omitting the blank column and breaking the data structure.

Before you start

Verify your data source files and check if the first column in your updated Excel workbook is completely blank before refreshing your Power Query connection.

Solution 1Recommended

Define the Source as an Explicit Excel Table

Converting your source data into a designated Excel Table prevents Power Query from relying on automatic used-range detection, locking in the column structure regardless of blank cells.

By default, Power Query looks at the used range of a worksheet. If the leftmost column is entirely empty, Excel assumes the data starts in the next column.

Using an explicit Table tells Power Query exactly where the data boundaries are, guaranteeing that even a completely empty Column A is imported.

Select the source data

Open the source Excel file, highlight your entire data range manually, ensuring you include the completely blank first column.

Insert a Table

Go to the 'Insert' tab on the ribbon and click 'Table' (or press Ctrl+T). Ensure 'My table has headers' is checked if applicable, and click OK.

Name the Table

In the 'Table Design' tab, give your table a recognizable name in the 'Table Name' box.

Update Power Query source

Open your Power Query Editor, go to the 'Source' step in 'Applied Steps', and change the M code to reference your newly created Excel Table instead of the raw worksheet.

Define the Source as an Explicit Excel Table

Consistent Structure: Using Tables is a best practice for Power Query as it dynamically expands with new rows while strictly maintaining your column headers.

Solution 2

Add a Placeholder Value to the Blank Column

If you cannot format the source as a Table, adding a placeholder value ensures Power Query still recognizes the column during its automatic range detection.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

If you frequently encounter complex formatting and import issues in Excel, consider switching to WPS Office. It provides a highly compatible, lightweight, and free alternative to Microsoft Office, ensuring smooth data management and a familiar user interface.

1. Download and Install: Visit the official WPS website to download the free WPS Office suite and install it on your computer.
2. Open your Spreadsheets: Launch WPS Spreadsheets and directly open your existing .xlsx files without worrying about compatibility issues.
3. Edit and Save: Manage your data using familiar tools and save your progress seamlessly.

Fully compatible with Microsoft Excel (.xlsx) formatsLightweight and fast to install on any deviceIntuitive spreadsheet interface for managing large datasets easilyFree alternative with no steep learning curve required

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query drop my first column when it is blank?

Power Query automatically detects the 'used range' of an Excel worksheet. If Column A has no data at all, Excel considers the used range to start at Column B, causing Power Query to skip the blank column.

Can I fix the missing column issue without modifying the source file?

Yes, if you cannot touch the source file, you can manually adjust the M code in the Power Query Advanced Editor. You can define the source range explicitly (e.g., 'Sheet1!A1:Z100') rather than letting Power Query auto-detect the sheet's used range.

Does this issue happen with CSV files imported into Power Query?

No, this used-range detection issue is specific to Excel workbook (.xlsx/.xls) sources. CSV files are read as structured text, so empty columns separated by commas are always preserved during import.

Excel & Data Problems