Power Query Problems

How to Create a Dynamic Number of Rows in Excel Power Query

Rana Garcia Oct 23, 2026 868 views

Question details

The user needs to generate a dynamic number of rows in Power Query based on the source data instead of a fixed amount.

How to Create a Dynamic Number of Rows in Excel Power Query

Product: Excel
Device & OS: not provided
Scenario: Updating Power Query so it automatically adjusts the number of rows when new records are added to the source dataset.
Observed behavior: The current Power Query M code adds a fixed number of rows, requiring manual code edits whenever the source data quantity changes.

Before you start

Ensure your source data is formatted as an Excel Table so Power Query can dynamically read the updated dataset size whenever you refresh the connection.

Solution 1Recommended

Calculate and Generate Rows Dynamically Using M Code

Use Power Query functions like List.Numbers combined with a dynamic row count from your source data to generate the exact number of rows required.

Power Query does not require traditional loop commands like 'For' or 'While' to generate rows. Instead, you can calculate the required row count directly from your source data and use list generation functions to dynamically output the correct number of rows.

Load your data to Power Query

Select your source data table in Excel, navigate to the Data tab, and click 'From Table/Range' to open the Power Query Editor.

Calculate the dynamic row count

Determine the required count by referencing a specific cell or counting the rows of your source table. Create a custom step (e.g., named RowCount) that holds this calculated integer value.

Generate a dynamic list

Add a new Custom Step and use the formula List.Numbers(1, RowCount). This automatically generates a list starting from 1 up to your dynamic row count.

Convert the list to a table

Navigate to the Transform tab and click 'To Table' to convert your dynamically generated list of numbers into actual table rows that update upon refresh.

Calculate and Generate Rows Dynamically Using M Code

No loop required: By using List.Numbers or List.Generate, Power Query processes the dynamic row generation highly efficiently without needing explicit loop syntax.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Management

While complex Power Query M-code scripts are specific to Microsoft Excel, WPS Office provides a free, lightweight, and highly compatible alternative for everyday data processing, analysis, and visualization.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite to your device.
2. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx or .csv files seamlessly without formatting loss.
3. Analyze your data: Use WPS Spreadsheet's built-in advanced formulas, pivot tables, and sorting tools to process and summarize your dynamic data efficiently.

Fully compatible with Microsoft Excel (.xlsx, .xls) formatsLightweight application that runs smoothly on older devicesBuilt-in advanced formulas, pivot tables, and charting toolsFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Power Query returning a fixed number of rows instead of updating dynamically?

This happens if the M code contains a hardcoded number (e.g., List.Numbers(1, 10)). To fix this, replace the fixed number with a dynamic variable that counts the rows or sums the specific values in your source data.

Can I use a traditional For or While loop in Power Query?

Power Query does not use traditional loops. Instead, it relies on iterative list functions like List.Generate or List.Numbers to achieve dynamic generation and filtering without explicitly writing standard loop logic.

Does WPS Office support Power Query M code?

WPS Spreadsheet does not natively support creating or editing advanced Power Query M code scripts. However, it offers robust and highly compatible data analysis tools like PivotTables, advanced filtering, and dynamic array formulas as alternative ways to manage your data.

Excel & Data Problems