Power Query Problems

How to Remove Null Values in Excel Power Query

Olivia Miller Oct 23, 2026 870 views

Question details

The user needs to clean up scattered null values generated across multiple columns after importing text files into Power Query.

How to Remove Null Values in Excel Power Query

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Importing raw text files into a data model resulting in misaligned rows and null entries.
Observed behavior: Scattered null values appear across several columns, requiring row consolidation and null filtering to make the dataset usable.

Before you start

Ensure your raw text data is fully loaded into the Power Query Editor and identify which columns contain the scattered null values that need to be consolidated.

Solution 1Recommended

Fill Up Values and Filter Nulls in Power Query

Use the Fill Up command to align scattered data into the correct rows, then filter out the remaining nulls.

When text files are imported incorrectly, data for a single record might be staggered across multiple rows. By filling the values upward, you consolidate the record into a single row before deleting the empty remnants.

Select affected columns

Hold down the Ctrl key and click the headers of the columns containing scattered null values to select them simultaneously.

Use Fill Up

Go to the Transform tab on the ribbon, click on the 'Fill' dropdown button, and select 'Up'. This will replace nulls by bringing up the non-null values from the rows directly below.

Filter out remaining nulls

Click the drop-down filter arrow on the header of a partially populated column, uncheck the '(null)' box, and click OK to remove the leftover empty rows.

Promote headers

Navigate to the Home tab and click 'Use First Row as Headers' so your dataset columns are properly labeled.

Fill Up Values and Filter Nulls in Power Query

Data Preserved: Using the Fill feature ensures your imported data is preserved and correctly aligned before you remove any rows, preventing accidental data loss.

Free Microsoft Office alternative

Clean and Manage Spreadsheet Data with WPS Office

If complex data modeling features like Power Query slow down your device, WPS Office provides a lightweight, highly compatible alternative. It handles standard data imports, filtering, and cleaning seamlessly, saving you time and system resources.

1. Download and Install: Download WPS Office for free from the official website and follow the installation prompts.
2. Open Your Data File: Launch WPS Spreadsheet and open your .xlsx or .csv file directly.
3. Clean Your Data: Use the built-in Data tab to apply filters, remove duplicates, or utilize the Text to Columns feature to organize messy text imports.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightweight installation that runs smoothly even on older devices.Built-in text import wizards and robust data filtering tools for quick cleanup.Free to use with a familiar, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do null values appear when importing text files into Power Query?

Null values often appear if the raw text file has irregular delimiters, varying line breaks, or blank rows. This causes Power Query to create empty fields when parsing the unstructured data into distinct columns.

What is the difference between Fill Up and Fill Down in Power Query?

Fill Up replaces null values by copying the next non-null value from the row directly below it. Fill Down does the exact opposite, copying the nearest non-null value from the row above into the null spaces below.

Can I remove rows with null values without using the Fill tool?

Yes, you can use the 'Remove Empty' option under 'Remove Rows' in the Home tab. However, doing this before using the Fill tool might cause you to lose misaligned data that was scattered across multiple rows.

How do I replace null values with a specific number like zero?

Right-click the column header, select 'Replace Values', type 'null' in the Value To Find box, and enter '0' in the Replace With box. Click OK to apply the changes.

Excel & Data Problems