Power QueryExercises

Remove blank rows and keep an explicit row order

Drop empty rows safely and keep a reliable record of source order.

~9 min read · intermediate · Module 5: Filtering and sorting rows

By Tahir Nas · Examples run in our simulator · Updated

Before this: Filter rows by value, null, empty text and errors are different things

Exports and hand-edited sheets often contain blank separator rows. A row is blank when every cell is null or empty text; a row with only some empty cells is real data with gaps. Remove the first kind, keep the second.

The Remove Blank Rows pattern

Home → Remove Rows → Remove Blank Rows writes a filter that looks at all the values in the row at once. Record.FieldValues(_) turns the current row into a list, and the row is kept if anything is left after removing nulls and empty text:

let
    Source = #table({"Item", "Qty", "Note"}, {
        {"Kite", 2, null},
        {null, null, null},
        {"", null, ""},
        {"Reel", null, "back order"}
    }),
    NoBlanks = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    NoBlanks

Result

ItemQtyNote
Kite2null
Reelnullback order

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The Reel row stays: its Qty is missing, but the row isn't blank.

Because the rule inspects every column, it keeps working when columns are added. A filter on a single column, such as [Item] <> null, would also drop real rows that happen to lack an item name.

Whitespace-only rows

A cell containing a single space isn't empty text, so the pattern above keeps a row of spaces. Trim text columns first (Transform → Format → Trim), or extend the rule to treat whitespace as empty.

Make row order explicit

Sometimes the source order is information: the sequence of entries in a log, or lines on an invoice. Power Query doesn't promise to keep row order through every operation, so capture it as data before you transform anything else:

let
    Source = #table({"Event"}, {{"opened"}, {"assigned"}, {"replied"}, {"closed"}}),
    Numbered = Table.AddIndexColumn(Source, "Seq", 1, 1, Int64.Type),
    Reversed = Table.Sort(Numbered, {{"Seq", Order.Descending}})
in
    Reversed

Result

EventSeq
closed4
replied3
assigned2
opened1

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

With Seq in the data, you can always restore or reverse the original order by sorting on it.

Where to find this in Excel

Excel for Windows
Home > Remove Rows > Remove Blank Rows
What you see
It sits in the same Remove Rows menu as Remove Duplicates and Remove Errors. Add Column > Index Column first if you want the original row order visible.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Filter by values in a column (Microsoft Learn); Working with duplicate values (Microsoft Learn); Add an index column (Microsoft Learn).

Common mistakes

  • Removing rows where one column is empty when the intent was to remove fully blank rows.
  • Forgetting that a cell with a space isn't empty, so whitespace rows survive.
  • Relying on the source order after merges or groups without an index column to restore it.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Home → Remove Rows → Remove Blank Rows generates the Record.FieldValues pattern shown here.
  • Add Column → Index Column → From 1 writes Table.AddIndexColumn in both hosts.
  • The simulator keeps row order deterministic, but don't rely on that in real hosts: use an index or sort explicitly when order matters.
Functions in this lesson:Table.SelectRowsRecord.FieldValuesList.RemoveMatchingItemsTable.AddIndexColumn

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Filtering and sorting quiz