Power Query Problems

How to Create a Restarting Sequence for Each Category in Power Query

Maira Mehtab Oct 1, 2026 868 views

Question details

The user needs to generate a sequence of numbers that starts at 1 and counts up to a specific number for each business unit or category, restarting at 1 when moving to the next category.

How to Create a Restarting Sequence for Each Category in Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Transforming spreadsheet data where each business unit must be expanded into distinct rows based on the number of people, with a sequential index generated per group.
Observed behavior: The goal is to automatically expand a numerical count column into a numbered list (1 to N) for each category, rather than maintaining a single static row per category.

Before you start

Ensure your dataset is loaded into Power Query as a table, and verify that the column determining the sequence count is formatted as a Whole Number (Integer) before proceeding.

Solution 1Recommended

Create a Restarting Sequence using M Code and List Expansion

The most efficient way to generate a restarting sequence per category is to add a custom column with a list {1..[Column Name]} and then expand it into new rows.

By using the curly bracket syntax in Power Query, you can instantly create a list of numbers from 1 up to the value specified in your count column. Expanding this list dynamically creates the sequential rows you need for each category.

Open Power Query Editor

Select your data range in Excel, go to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Format the Count Column

Locate the column containing the number of items or people (e.g., 'Sales people'). Click the data type icon in the column header and select 'Whole Number'.

Add a Custom Column

Navigate to the Add Column tab and click 'Custom Column'. In the formula box, type `={1..[Sales people]}` (replacing '[Sales people]' with your exact column name) and name the column 'Sequence'.

Expand to New Rows

Click the expand icon (two diverging arrows) at the top right of the new 'Sequence' column header and select 'Expand to New Rows'.

Clean Up and Load

Right-click the original count column ('Sales people') and select 'Remove', as it is no longer needed. Click 'Close & Load' on the Home tab to import the new data into your spreadsheet.

Create a Restarting Sequence using M Code and List Expansion

Advanced Editor Shortcut: If you are familiar with the Advanced Editor, you can directly paste the M code: `Table.ExpandListColumn(Table.AddColumn(Source, "Sequence", each {1..[Sales people]}), "Sequence")` to apply these steps automatically.

Free Microsoft Office alternative

Need a simpler way to manage your spreadsheets? Try WPS Office

While Power Query provides advanced programmatic data transformations, WPS Office offers a free, lightweight, and highly compatible alternative for everyday spreadsheet tasks. Enjoy a familiar interface and seamless handling of all your Excel files.

1. Download WPS Office: Visit the official WPS website and download the free software suite.
2. Open your Excel File: Launch WPS Spreadsheets and open your existing .xlsx data files with zero formatting loss.
3. Analyze your Data: Use built-in sorting, filtering, and calculation features to organize and track your business categories effortlessly.

Seamless compatibility with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in intuitive data processing, filtering, and pivot table tools.Free, lightweight, and fast installation on Windows, Mac, and Linux.Familiar tabbed interface that makes switching incredibly easy without learning a new layout.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting an error when typing the custom column formula in Power Query?

This usually happens if the column referenced for the sequence end (e.g., [Sales people]) is not formatted as a Whole Number. Ensure you have converted the column's data type to Integer before creating the `{1..[Column Name]}` list.

Can I start the Power Query sequence at a number other than 1?

Yes, you can easily modify the custom column formula. For example, if you want the sequence to start at 5 and end at the value in your column plus 4, you could use the formula `{5..([Sales people]+4)}`.

What if I need to restart the sequence based on a category change instead of a count column?

If you don't have a count column and instead need to number existing rows by group, you should group your data by the category column first, add an index column to each grouped table using `Table.AddIndexColumn`, and then expand the grouped tables.

Does Power Query automatically update this category sequence if the source data changes?

Yes. Once you set up the Power Query transformation, you can simply click 'Refresh All' on the Data tab whenever your source data updates, and the sequences will recalculate and expand automatically.

Excel & Data Problems