Power Query Problems

How to Combine Text Blocks Separated by Null Rows in Power Query

Kushani NimanthikaKushani Nimanthika Nov 10, 2026 871 views

Question details

The user needs to merge multiple rows of text that are divided by blank (null) rows into a single cell per block, outputting the result in a new column or table.

How to Combine Text Blocks Separated by Null Rows in Power Query
Product
Excel
Device & OS
not provided
Scenario
Cleaning and transforming a dataset where cohesive text entries are scattered across multiple rows of varying lengths, separated by blank rows.
Observed behavior
Text data is distributed across multiple separate rows instead of being combined into a single, structured cell for each logical block.
Before you start

Ensure your dataset is loaded into Power Query and that the empty rows separating your text blocks are truly evaluated as 'null' rather than containing invisible spaces or empty strings.

Solution 1Recommended

Use an Index Column and Conditional Grouping

This method uses the Power Query interface to create a unique grouping identifier based on the null rows, then merges the text blocks using Text.Combine.

By assigning a unique ID to each text block, you can easily group variable-length rows together and combine their text contents into a single cell.

1
Add an Index Column

Navigate to the 'Add Column' tab in the Power Query Editor, click 'Index Column', and select 'From 0'.

2
Create a Conditional Group ID

Go to 'Add Column' > 'Conditional Column'. Set the condition: If your target text column 'equals' 'null', then output the 'Index' column; otherwise, output 'null'.

3
Fill Down the Group ID

Right-click the new conditional column header, select 'Fill', and then choose 'Down'. This assigns the same ID to all rows within a specific text block.

4
Filter Out Null Rows

Click the filter dropdown on your original text column and uncheck 'null' to remove the blank separator rows from the dataset.

5
Group By and Combine Text

Select the Group ID column, go to the 'Transform' tab, and click 'Group By'. In the dialog, set the New column name to 'CombinedText' and Operation to 'All Rows'. Afterwards, edit the generated M code in the formula bar to replace the aggregation portion with: each Text.Combine([ColumnName], "#(lf)")

Use an Index Column and Conditional Grouping
Line Feed Formatting: Using "#(lf)" inside Text.Combine ensures that each original row of text starts on a new line within the combined cell.
Free Microsoft Office alternative

Handle Complex Data and Spreadsheets with WPS Office

While advanced M-code Power Query operations are specific to Microsoft Excel, you can seamlessly manage, edit, and transform complex datasets using WPS Office. It provides a lightweight, highly compatible environment for your data analysis needs without the heavy subscription costs.

  1. 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
  2. 2. Open Your Dataset: Launch WPS Spreadsheet and open your existing Excel workbook without worrying about format compatibility.
  3. 3. Transform Data: Utilize powerful built-in text functions like TEXTJOIN and advanced data filtering to clean and combine your text data effortlessly.
Fully compatible with Microsoft Excel (.xlsx) formatsLightweight application with fast processing for large datasetsBuilt-in advanced formulas, pivot tables, and data cleaning toolsFree to use with an intuitive, familiar user interface
microsoft office alternative - wps office

Frequently Asked Questions

What does #(lf) mean in Power Query?

In Power Query M code, #(lf) represents a Line Feed (newline). It is used as a delimiter in functions like Text.Combine to ensure that each merged text snippet starts on a new line within the same cell.

Why are my blank rows not recognized as null in Power Query?

If your rows contain empty strings ("") or hidden space characters instead of being completely empty, Power Query evaluates them as text rather than 'null'. You must first clean the data by using 'Replace Values' to convert empty spaces or strings to null.

Can I output the combined text to a new table instead of replacing the original?

Yes. Before applying the grouping and combining steps, you can right-click your original query and select 'Duplicate' or 'Reference'. This allows you to perform the transformation in a new query while keeping your source table intact.

Does this grouping method work with variable-length text blocks?

Yes. Because the grouping relies on the null row delimiters (or consecutive non-null flags), the Text.Combine function will merge all text rows that belong to the same block, regardless of whether the block spans two rows or twenty rows.