Power Query Problems

Use Excel Power Query to Convert Variable-Size CSV Files to XLSX

Emma BrownEmma Brown Nov 12, 2026 868 views

Question details

Import CSV files with changing row counts, remove rows whose column L value is zero, and create an Excel table without hard-coded row limits.

Use Excel Power Query to Convert Variable-Size CSV Files to XLSX
Product
Microsoft Excel Power Query and Office Scripts
Device & OS
not provided
Scenario
The user needs a repeatable CSV-to-XLSX workflow that works when each source CSV contains a different number of populated rows.
Observed behavior
A fixed row range in an Office Script does not adapt when the imported CSV contains more or fewer rows than expected.
Before you start

Confirm that the CSV has a consistent header row and that the values to evaluate are in column L. In Excel desktop, use a current version that includes the Data tab and Get & Transform Data tools.

Solution 1Recommended

Import and filter the variable-size CSV with Excel Power Query

Use this recommended method when you want a refreshable import that automatically handles changing CSV row counts.

Power Query reads the available data from the CSV instead of relying on a predefined worksheet range. You can apply the filter once and refresh the query whenever a new CSV is available.

1
Start a Text/CSV import

In Excel, select Data > From Text/CSV, choose the CSV file, and review the preview in the import window.

2
Open Power Query Editor

Select Transform Data to open Power Query Editor before loading the file into the workbook.

3
Filter column L

Select the column that corresponds to column L, open its filter arrow, and clear the value 0. If the column is numeric, use Number Filters > Does Not Equal and enter 0.

4
Load the cleaned data as a table

Select Home > Close & Load To, choose Table, and select the worksheet location for the imported results.

5
Save the workbook as XLSX

Select File > Save As and choose Excel Workbook (*.xlsx). To process updated source data later, select Data > Refresh All.

Import and filter the variable-size CSV with Excel Power Query
No fixed row limit is required: Power Query detects the rows in the imported CSV and applies the same transformation steps when you refresh the query.
Free Microsoft Office alternative

Open, filter, and save CSV data with WPS Spreadsheet

If you need a free, lightweight spreadsheet application for everyday CSV cleanup and workbook conversion, WPS Office provides a familiar spreadsheet interface and supports a smooth transition from Microsoft Office files. Open the CSV in WPS Spreadsheet, filter the relevant column, and save the result as an Excel-compatible workbook.

Open CSV files and filter data directly in WPS Spreadsheet.Save edited spreadsheets in Microsoft Excel XLSX format.Use a familiar ribbon-style interface for a seamless Microsoft Office migration.Lightweight, free office suite for routine spreadsheet conversion and data cleanup.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query work better than a fixed Office Script range for CSV files?

Power Query imports the rows that exist in the selected CSV at refresh time. Unlike a fixed range such as A1:L145, it does not require you to change the row limit when the source file grows or shrinks.

How do I exclude rows where column L equals zero in Power Query?

In Power Query Editor, select the column corresponding to column L, open the filter menu, and exclude 0. For numeric data, select Number Filters > Does Not Equal and enter 0.

Can Power Query load the filtered CSV results into an Excel table?

Yes. After applying transformations, select Home > Close & Load To, choose Table, and select the worksheet destination. Excel creates a table from the query output.

Can Office Scripts import a CSV file from my computer by themselves?

Office Scripts primarily automate workbook content and do not directly browse local files. Use Power Query for interactive CSV imports, or use Power Automate with Office Scripts for an automated file-based workflow.

How do I update the table when I receive a new CSV file?

If the query points to the same file path, replace the source CSV and select Data > Refresh All. If the file name or location changes, update it through Data > Queries & Connections and edit the query source settings.