Power Query Problems

How to Fix Power Query Not Splitting Columns Correctly Beyond 1000 Rows

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to split a semicolon-separated column into five distinct columns, but Power Query is only generating three columns for a dataset containing 1,188 rows.

Product: Power Query
Device & OS: not provided
Scenario: Splitting a single text column into multiple columns based on a delimiter in a dataset exceeding 1000 rows.
Observed behavior: The Power Query split operation limits the output to three columns, ignoring rows further down the dataset that contain up to five delimited values.

Before you start

Verify the exact delimiter used in your dataset and determine the absolute maximum number of values present in a single cell before modifying your query settings.

Solution 1Recommended

Specify the Output Column Count in Advanced Options

Force Power Query to generate the exact number of columns required by configuring the advanced split settings manually.

By default, Power Query generates its UI preview and output schema based on the first 1,000 rows. If the row containing the maximum number of delimiters appears after row 1000, the UI will not account for it automatically.

Initiate Split Column

In the Power Query Editor, select your target column, navigate to the Transform tab, click 'Split Column', and choose 'By Delimiter'.

Select Delimiter

Choose your specific delimiter, such as a 'Semicolon', from the drop-down menu.

Expand Advanced Options

Click on 'Advanced options' at the bottom of the dialog box to reveal additional settings.

Set Column Count

In the 'Number of columns to split into' field, type '5' (or your known maximum number of columns) and click 'OK'.

Successful Configuration: Specifying the exact column count overrides the 1,000-row preview limitation and ensures all data is retained across the correct number of columns.

Solution 2

Remove Hardcoded Column Lists in the Advanced Editor

Modify the underlying M code to remove fixed column limits, allowing Power Query to evaluate all rows dynamically during the data refresh.

Free Microsoft Office alternative

Need a Simpler Way to Split Columns? Try WPS Office

If troubleshooting Power Query scripts becomes overwhelming, WPS Office offers a highly compatible and lightweight alternative. With WPS Spreadsheet, you can effortlessly split text into multiple columns for millions of rows using built-in data tools—no complex coding required.

1. Download and Install: Get WPS Office for free from the official website and open your spreadsheet.
2. Select Your Data: Highlight the entire column containing the delimited values you need to split.
3. Use Text to Columns: Navigate to the 'Data' tab, click on 'Text to Columns', select your delimiter (like a semicolon), and let WPS instantly separate your data across the necessary columns.

Fully compatible with Microsoft Excel formats (.xlsx, .csv).Easily split data into columns using the intuitive Text-to-Columns wizard.Lightweight application that runs smoothly on most operating systems.Free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query only evaluate the first 1000 rows by default?

Power Query generates previews and auto-detects schema based on the first 1,000 rows to ensure the editor remains fast and responsive. Evaluating the entire dataset for every step in large queries would severely impact performance during the design phase.

Can I force Power Query to profile the entire dataset?

Yes. At the bottom left of the Power Query Editor, you can click on the 'Column profiling based on top 1000 rows' text and change it to 'Column profiling based on entire dataset'. However, this may cause the editor to slow down significantly on massive tables.

Is there a way to split data without using Power Query?

Yes, standard spreadsheet tools like the 'Text to Columns' feature found in the Data tab of both Microsoft Excel and WPS Spreadsheet can split delimited data directly within the worksheet without opening a query editor.

Excel & Data Problems