Power Query Problems

How to Remove Cascading Null Cells in Excel Power Query

Amos Gikunda Oct 16, 2026 869 views

Question details

The user needs to remove cascading null cells generated after transforming nested text-file columns in Power Query, while retaining the expected values.

How to Remove Cascading Null Cells in Excel Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Transforming data from nested text files where the output leaves required values alongside empty cascading cells.
Observed behavior: The query output successfully extracts the required values but leaves unwanted cascading null cells in the resulting table.

Before you start

Before proceeding, duplicate your original query in the Power Query Editor by right-clicking it and selecting 'Duplicate', so you can safely test the index merging steps without altering your current progress.

Solution 1Recommended

Use Index Columns and Self-Merge Tables

Align related rows by adding index columns and merging the table with itself to consolidate the required values and eliminate nulls.

When transforming nested data, relationships between expanded rows can be lost, creating cascading nulls. Adding index columns helps Power Query track these relationships so you can merge the data back onto a single row.

Add an Index Column

In the Power Query Editor, go to the 'Add Column' tab, click on 'Index Column', and select 'From 0' or 'From 1'. Create a total of three index columns to establish the required row relationships.

Merge the Table with Itself

Navigate to the 'Home' tab and click 'Merge Queries'. In the dialog box, select your current table as both the top and bottom tables, then click the index columns that form your first relationship to join them.

Expand the Merged Columns

Click the small expand icon in the header of the newly created merged column. Uncheck 'Use original column name as prefix' and select the columns containing the matching data you want to bring into the row.

Repeat for the Second Relationship

If your nested data requires it, click 'Merge Queries' again to join the table on the second index relationship, then expand the necessary columns just as you did in the previous step.

Clean Up Temporary Columns

Hold the Ctrl key and click the headers of the index columns and any other temporary columns you no longer need. Right-click any selected header and choose 'Remove Columns'.

Filter Out the Null Values

Click the filter dropdown arrow on your target data column, uncheck the '(null)' box, and click 'OK' to clear out the remaining cascading blank cells.

Data Format Tip: Creating a small mock-up table in an Excel worksheet before applying these steps can make it easier to visualize the index relationships and formulate the exact steps.

Free Microsoft Office alternative

Looking for a Lightweight Alternative to Excel?

While complex Power Query transformations are deeply native to Microsoft Excel, WPS Office Spreadsheet provides a fast, lightweight, and highly compatible alternative for everyday data analysis, filtering, and formatting without a steep learning curve.

Free to use with a familiar, tabbed user interface.Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Easily handle data filtering, sorting, and pivot tables.Lightweight installation that runs smoothly on most devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query create cascading null cells during transformation?

Cascading null cells typically occur when expanding nested tables, JSON records, or text files where multiple hierarchical data points lack explicit alignment, causing Power Query to stagger the extracted values across multiple new rows.

Can I remove null values without using index columns?

Yes, depending on your data structure, you can select the column with data gaps, go to the 'Transform' tab, click 'Fill', and choose 'Down' or 'Up'. This populates the null cells with the adjacent values, allowing you to easily filter out duplicates or remaining blank rows afterwards.

How do I quickly filter out null values from multiple columns at once?

Instead of filtering each column individually, you can go to the 'Home' tab, click on 'Remove Rows', and select 'Remove Blank Rows'. This will instantly clear out any row where all selected column values are null.

Excel & Data Problems