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
NoBlanksResult
| Item | Qty | Note |
|---|---|---|
| Kite | 2 | null |
| Reel | null | back order |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
ReversedResult
| Event | Seq |
|---|---|
| closed | 4 |
| replied | 3 |
| assigned | 2 |
| opened | 1 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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.
Record.FieldValuesList.RemoveMatchingItemsTable.AddIndexColumnNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Filtering and sorting quiz