Power Query Problems

How to Split Variable-Length Excel Data Groups Using Power Query

Algirdas Jasaitis Oct 16, 2026 869 views

Question details

The user needs to identify and categorize variable-length groups of data from a daily CSV report that contains repeated headers but lacks a dedicated grouping column.

How to Split Variable-Length Excel Data Groups Using Power Query

Product: Excel
Device & OS: not provided
Scenario: Importing and processing a daily CSV report with irregular row counts grouped under repeated headers (e.g., Type 1, Type 2).
Observed behavior: The data rows are mixed in a continuous list under various header rows without an identifying column, making it difficult to filter or build pivot tables.

Before you start

Ensure your daily CSV file is loaded into the Power Query Editor and identify the specific column that contains the repeated group header text.

Solution 1Recommended

Use a Conditional Custom Column and Fill Down

Identify group headers by creating a conditional column and use the Fill Down feature to categorize the variable-length rows underneath.

Instead of attempting to split the data into multiple disconnected tables right away, the most robust method is to assign an identifier to every row. By creating a custom column that extracts the header name and filling that name down to the subsequent rows, you create a structured dataset that is easy to filter or summarize.

Add a Custom Column

In the Power Query Editor, go to the 'Add Column' tab and click 'Custom Column'. Write an 'if' statement using Text.Contains to identify the group label. For example: if Text.Contains([Column1], "Renewal", Comparer.OrdinalIgnoreCase) then [Column1] else null.

Apply Fill Down

Select your newly created custom column. Navigate to the 'Transform' tab, click the 'Fill' dropdown, and select 'Down'. This replaces all null values with the group label immediately above them.

Filter Out Original Headers

Now that every row is categorized, click the filter arrow on your original data column to uncheck and remove the repeated header rows, leaving only the actual data.

Load the Organized Data

Click 'Close & Load' on the Home tab to output your newly organized, single table back into an Excel worksheet, where it is now ready for Pivot Tables.

Use a Conditional Custom Column and Fill Down

Pro Tip for Conditional Formulas: Always include 'Comparer.OrdinalIgnoreCase' as the third argument in your Text.Contains function to prevent the formula from failing due to inconsistent uppercase or lowercase letters in your CSV file.

Free Microsoft Office alternative

A Lightweight Alternative for Daily Spreadsheet Data Analysis

While advanced Power Query data shaping is specific to Microsoft Excel, WPS Office provides a free, highly compatible, and lightning-fast spreadsheet tool perfect for managing large CSV files, filtering data, and creating Pivot Tables.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open Your CSV: Launch WPS Spreadsheet and open your daily CSV report directly without any complex import wizards.
3. Filter and Analyze: Use the intuitive Data filtering and Pivot Table features to organize, split, and analyze your data sets.

Free to download and use for all your essential productivity needsFully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Lightweight architecture ensures fast performance for large datasetsBuilt-in advanced filtering and pivot table tools to analyze grouped data seamlessly

microsoft office alternative - wps office

Frequently Asked Questions

What does the Text.Contains function do in Power Query?

Text.Contains checks a specified column to see if it includes a specific text string or substring. In this scenario, it is used to detect if a row contains a group header keyword like 'Renewal' or 'Type 1'.

How does the Fill Down command work?

The Fill Down command replaces null (empty) values in a selected column with the nearest non-null value located directly above them. This makes it the perfect tool for assigning a single header category to multiple subsequent rows of variable lengths.

Can I output these groups into separate Excel worksheets?

Yes. Once you have created the group identifier column using Fill Down, you can right-click the query and duplicate it. Filter each duplicated query by a specific group type, and they will load into separate worksheets.

Why is my custom conditional column only returning null values?

This usually happens due to case sensitivity. Ensure the search text in your formula exactly matches the casing of the data, or add Comparer.OrdinalIgnoreCase to your Text.Contains formula to ignore text casing entirely.

Excel & Data Problems