Power Query Problems

How to Split Semicolon-Separated Values Beyond 1,000 Rows in Power Query

Khadija Khan Sep 28, 2026 869 views

Question details

The user needs to ensure Power Query correctly splits all semicolon-separated values in an Excel dataset when rows exceeding the preview limit contain more delimited items than the earlier rows.

How to Split Semicolon-Separated Values Beyond 1,000 Rows in Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Splitting a column containing varying numbers of semicolon-separated values (e.g., email addresses) in a large dataset.
Observed behavior: Power Query evaluates only the first few rows, creating a limited number of output columns (e.g., 3 columns), and ignores the 4th or 5th delimited items present in rows further down the dataset.

Before you start

Before modifying your query, determine whether you know the absolute maximum number of separated values a single row might contain, or if you require a dynamically updating column structure.

Solution 1Recommended

Specify the Expected Number of Columns Manually

Use the Advanced Split Options in Power Query to force the creation of the required number of columns, ensuring data further down the list is not truncated.

By default, Power Query examines the first 1,000 rows to infer how many output columns are needed. If the maximum number of delimiters only appears after row 1,000, you must override this automatic detection by explicitly telling Power Query how many columns to generate.

Select the target column

Open the Power Query Editor and click on the column header that contains the semicolon-separated values.

Open Split Column settings

On the Home tab, click 'Split Column' and select 'By Delimiter' from the drop-down menu.

Set the delimiter type

In the Split Column by Delimiter dialog box, choose 'Semicolon' from the Select or enter delimiter drop-down list.

Configure advanced options

Click to expand the 'Advanced options' section at the bottom of the dialog box.

Define the column count

In the 'Number of columns to split into' field, manually enter the maximum number of columns you expect (e.g., 5), and click OK to apply the transformation.

Specify the Expected Number of Columns Manually

Handling Empty Values: Rows that contain fewer email addresses than the specified maximum will automatically populate the remaining output columns with 'null', keeping your data structure intact.

Solution 2

Use Dynamic Power Query M Code

Edit the Power Query formula directly to remove the hardcoded column list, allowing the query to calculate the required columns dynamically.

Split Large Delimited Datasets Effortlessly in WPS Spreadsheet

If you want to avoid complex query configurations and preview limitations, WPS Spreadsheet provides a straightforward Text to Columns tool. It evaluates your entire selected dataset instantly, perfectly splitting varying semicolon-separated values across tens of thousands of rows without missing a single item.

1. Select your data: Open your dataset in WPS Spreadsheet and select the entire column containing the semicolon-separated values.
2. Launch Text to Columns: Navigate to the 'Data' tab on the top ribbon and click the 'Text to Columns' button.
3. Choose Delimited format: In the wizard, select 'Delimited' as the original data type and click Next.
4. Set the Semicolon delimiter: Check the box next to 'Semicolon'. The data preview will immediately show all columns separating correctly. Click Finish to apply the split to the whole sheet.

Easily split text by semicolons, commas, or custom delimiters in secondsProcesses entire columns instantly without arbitrary row preview limitationsFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Intuitive step-by-step wizard requires no coding or advanced configurations

microsoft office alternative - wps office

Frequently Asked Questions

Does Power Query have a hard limit of 1,000 rows for processing data?

No. The 1,000-row limit in Power Query only applies to the data preview loaded in the editor interface to optimize performance. When the query is loaded or refreshed, it processes all rows in the dataset. However, structure inferences (like how many columns to create) are based solely on that initial preview.

Why does Power Query ignore values in later rows when splitting columns?

When you perform a split operation using the standard UI, Power Query looks at the first 1,000 rows, determines the maximum number of delimiters in that sample, and hardcodes that specific number of output columns into the M code. If a row with more delimiters exists further down, those extra values have no corresponding column and are dropped.

Can I split the semicolon-separated values into rows instead of columns?

Yes. If you want to maintain a single column structure, you can choose to split values into new rows. In the Split Column by Delimiter dialog box, expand 'Advanced options' and select 'Rows' instead of 'Columns' under the 'Split into' section.

Excel & Data Problems