How to Fix Power Query Not Importing the Last CSV Column
Question details
Power Query is failing to import the final column of a CSV file during the data load process.
- Product
- Power Query
- Device & OS
- not provided
- Scenario
- Importing a CSV file using Power Query where the source data structure has recently changed or expanded.
- Observed behavior
- The final column or newly added columns of the CSV file are omitted from the loaded dataset because an early query step uses a fixed column count.
Duplicate your query or back up your spreadsheet before making changes to the M code to prevent accidental data loss if a syntax error occurs.
Remove the Fixed Column Count in the Source Step
Modify the M code in the first query step to allow Power Query to dynamically detect all columns in your CSV file rather than limiting it to a hardcoded number.
When you initially import a CSV, Power Query often automatically writes a step that fixes the number of columns based on the file's current state. Removing this fixed parameter forces Power Query to dynamically evaluate the column count upon every refresh.
In your spreadsheet application, navigate to the Data tab, click on 'Get Data', and select 'Launch Power Query Editor'.
In the 'Applied Steps' pane on the right side of the editor, click on the very first step, which is usually named 'Source'.
Look at the formula bar at the top of the window (enable it from the View tab if it is hidden). Locate the Csv.Document function, which will look something like `Csv.Document(File.Contents("..."), [Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None])`.
Carefully delete the `Columns=10, ` portion from the formula, or change the number to match the new total number of columns in your CSV.
Press Enter to apply the updated formula. Refresh the preview to confirm that the missing last column is now successfully importing into the editor.
Try WPS Office for Seamless Data Management
While Power Query is a powerful tool in Microsoft Excel, it often requires delving into complex M code to fix structural issues. WPS Office provides a lightweight, highly compatible, and free alternative for managing spreadsheets, importing CSV data intuitively, and handling large datasets without code-related hurdles.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
- 2. Open WPS Spreadsheet: Launch the application and open the WPS Spreadsheet module.
- 3. Import CSV Data: Go to the Data tab and use the 'Import Data' function to easily load your CSV files with full format compatibility.
Frequently Asked Questions
Why did my query work for months before missing the last column?
When you first created the query, Power Query recorded the exact number of columns present in the CSV file at that time. If a new column was recently added to the end of the source file, the hardcoded limit in the M code ignored it, causing the final column to be missing.
How do I view the full M code for my query?
You can view the entire script by navigating to the Home tab in the Power Query Editor and clicking on 'Advanced Editor' located in the Query group. This will display the complete M code for all applied steps.
Is it better to increase the column number or remove the argument entirely?
Removing the column argument entirely is generally the better practice because it makes your query dynamic. If your CSV file adds more columns in the future, Power Query will automatically detect and include them without requiring you to manually edit the code again.
