How to Combine Text Blocks Separated by Null Rows in Power Query
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.

- 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.
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.
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.
Navigate to the 'Add Column' tab in the Power Query Editor, click 'Index Column', and select 'From 0'.
Go to 'Add Column' > 'Conditional Column'. Set the condition: If your target text column 'equals' 'null', then output the 'Index' column; otherwise, output 'null'.
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.
Click the filter dropdown on your original text column and uncheck 'null' to remove the blank separator rows from the dataset.
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 Table.Group with GroupKind.Local
Directly edit the M code to group consecutive non-null rows together using the GroupKind.Local parameter.
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. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Dataset: Launch WPS Spreadsheet and open your existing Excel workbook without worrying about format compatibility.
- 3. Transform Data: Utilize powerful built-in text functions like TEXTJOIN and advanced data filtering to clean and combine your text data effortlessly.

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.




